cr_tables.sql
62.3 KB
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
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
begin;
\i plpgsql_funcs/cr_func_generic.sql
COMMENT ON DATABASE shop IS
'<H3><U>Allgemeine Kommentare:</U></H3>'
'die Vergabe der Triggernamen folgt einem bestimmten Schema:<BR><BR>'
' <TABLE>_trigger<NR>_on_'
'[change|insert|update|delete]_<DESCR><BR><BR>'
'wobei <TABLE> der Tabellenname ist, fuer den der Trigger definiert '
'wurde <NR> eine fortlaufende Nummer (1,2,...,n) fuer die zu der '
'Tabelle definierten Trigger, "change" steht fuer "insert und update" und '
'<DESCR> ist eine frei vergebbare Beschreibung dessen was der '
'Trigger tut. Dieses Schema dient hauptsaechlich dem Zweck eine '
'Reihenfolge fuer die Ausfuehrung der Trigger definieren zu koennen. '
'Da postgreSQL die Trigger zu einer Aktion in alphabetisch aufsteigender '
'Reihenfolge ausfuehrt laesst sich uber <NR> bestimmen in welcher '
'Reihenfolge die Trigger ausgefuehrt werden, <BR><BR>'
' ..._trigger1_... => ..._trigger2_... => ...<BR><BR>'
'<HR><BR>'
'ich muss zumindest ganz am schluss aber besser noch frueher Transaktionen '
'in meine Funktionen einbauen. Ich glaube das diese idealerweise in den '
'plpgsql Funktionen eingebaut werden, dann kann man spaeter in PHP oder '
'womit auch immer diese Funktionen nutzen und hat automatisch bessere '
'Performance....aus demselben Grund ist es im Moment noch nicht unbedingt '
'notwendig transaktionen einzubauen, wenn konsequent hauptsaechlich die '
'Funktionen genutzt werden.<BR>'
'OK, ich revidiere meine Meinung, Transaktionen sollten im hoeheren Level '
'implementiert werden, alleine schon deshalb weil BEGIN WORK nicht '
'innerhalb einer plpgsql Funktion nicht funtioniert, wenn krasse Feler in '
'plpgsql Funktionen auftreten (solche die sich nicht beheben lassen) '
'sollte man konsequent RAISE EXCEPTION nutzen um den ganzen Funktionsblock '
'abzubrechen, dadurch werden auch alle aenderungen die die Funktion '
'gemacht hat wieder zurueckgesetzt.<BR><BR>'
'<HR><BR>'
'alle Kommentare in der Datenbank muessen zu HTML konvertiert werden.'
'<BR><BR><HR>';
-- generic Users
-- [
-- create group guest;
-- create group customer;
-- create group admin;
-- create user guest ENCRYPTED PASSWORD 'guest'
-- NOCREATEDB NOCREATEUSER in group guest;
-- ]
-- Schemas
-- [
create schema trigger_func; -- Schema fuer Triggerfunktionen
COMMENT ON SCHEMA trigger_func IS 'Schema fuer Triggerfunktionen';
create schema adresse; -- alles zu Adressdaten
COMMENT ON SCHEMA adresse IS 'alles zu Adressdaten';
create schema person; -- alles zu Personen
COMMENT ON SCHEMA person IS 'alles zu Personen';
-- nur zum testen [ --
grant USAGE ON SCHEMA person TO GROUP guest;
-- ] --
create schema firma; -- alles zu Firmen
COMMENT ON SCHEMA firma IS 'alles zu Firmen';
create schema fibu; -- alles zu Konten etc.
COMMENT ON SCHEMA fibu IS 'alles zu Konten etc.';
create schema waren; -- alles zu Warengruppen und Produkten
COMMENT ON SCHEMA waren IS 'alles zu Warengruppen und Produkten';
grant USAGE ON SCHEMA waren TO GROUP guest;
create schema benutzer; -- alles zur Benutzerverwltung
COMMENT ON SCHEMA benutzer IS 'alles zur Benutzerverwltung';
-- ]
-- ende Schemas --
-- meine Aktionshistory
-- [
CREATE TABLE history (
"username" text NOT NULL, -- der User der die Aktion ausgeloest hat.
"time" timestamp NOT NULL, -- wann wurde die Aktion ausgeloest.
"tab" text NOT NULL, -- der betroffene Table.
"row_oid" int4 NOT NULL, -- die OID der betroffenen Zeile.
"at_time" text NOT NULL, -- BEFORE, AFTER.
"operation" text NOT NULL, -- INSERT, UPDATE, DELETE.
"col_names" text[] NOT NULL, -- zur uebersicht die column-namen
"col_types" text[] NOT NULL, -- zur uebersicht die column-typen
"col_vals" text[] NOT NULL); -- aktuelle Feldwerte
set search_path to trigger_func;
\i trigger_funcs/cr_trigger_func_generic.sql
set search_path to public;
-- ]
-- ende History
-- Table zu Länder --
-- [
set search_path to adresse;
create table land (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"land" text NOT NULL, -- Name(Bezeichnung) des Landes
"lnd_kz" varchar(3) NOT NULL, -- intern. Post-Kennzeichen
"vorwahl_l" varchar(4) NOT NULL); -- intern. Laendervorwahl
COMMENT ON TABLE land IS 'Tabelle zu Laender';
COMMENT ON COLUMN land._id_ IS 'interne id';
COMMENT ON COLUMN land.land IS 'Name (Bezeichnung) des Landes';
COMMENT ON COLUMN land.lnd_kz IS 'internationales Post-Kennzeichen';
COMMENT ON COLUMN land.vorwahl_l IS 'internationale Telefonvorwahl des Landes';
create unique index "land_ukey1" on land (land);
create unique index "land_ukey2" on land (lnd_kz);
create unique index "land_ukey3" on land (vorwahl_l);
\i plpgsql_funcs/cr_func_land.sql
select public.start_logging('land', 'ALL');
-- ]
-- Ende Länder --
-- Table zu Orte --
-- [
create table "ort" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"ort" text NOT NULL, -- Name (Bezeichnung) des Orts
"vorwahl_o" varchar(10) NOT NULL, -- Ortsvorwahl
"id_land" int4 NOT NULL, -- Land, in dem der Ort liegt
CONSTRAINT fk_ort1 FOREIGN KEY (id_land) REFERENCES land (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE ort IS 'Tabelle zu Orten';
COMMENT ON COLUMN ort._id_ IS 'interne id';
COMMENT ON COLUMN ort.ort IS 'Name (Bezeichnung) des Ortes';
COMMENT ON COLUMN ort.vorwahl_o IS 'Ortsvorwahl';
COMMENT ON COLUMN ort.id_land IS 'FOREIGN KEY: Land in dem der Ort liegt';
COMMENT ON CONSTRAINT fk_ort1 ON ort IS '
wird ein Land geloescht so muessen auch alle dazu gehörenden Orte
geloescht werden da moeglicherweise Orte mit gleichen Namen und
gleicher Vorwahl zu verschiedenen Laendern gehoeren und dadurch
Inkonsistenzen entstehen koennen.';
create unique index "ort_ukey1" on ort (vorwahl_o,id_land);
\i plpgsql_funcs/cr_func_ort.sql
select public.start_logging('ort', 'ALL');
-- ]
-- ende Orte --
-- Table zu Adressen --
-- [
create table "adresse" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"strasse" text NOT NULL, -- Strassenname
"h_nr" varchar(5) NOT NULL, -- Hausnummer
"plz_a" varchar(10) NOT NULL, -- Postleitzahl
"id_ort" int4 NULL, -- Ort
"id_land" int4 NOT NULL, -- Land
CONSTRAINT fk_adresse1 FOREIGN KEY (id_ort) REFERENCES ort (_id_)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT fk_adresse2 FOREIGN KEY (id_land) REFERENCES land (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE adresse IS 'Tabelle zu Adressen:
------------------
id_land habe ich mit aufgenommen, da es sein kann das ich den Ort zu
einer Adresse nicht kenne, aber weiß in welchem Land sie liegt, dann
kann ich nicht über den Ort das Land ermitteln, was man nach einer
ersten Ueberlegung fuer den logischeren Weg halten koennte.
Das Land zu einer Adresse muss bekannt sein, damit das passenden
Laenderkennzeichen zur Postleitzahl ergaenzt werden kann.';
COMMENT ON COLUMN adresse._id_ IS 'interne id';
COMMENT ON COLUMN adresse.strasse IS 'Strassenname';
COMMENT ON COLUMN adresse.h_nr IS 'Hausnummer';
COMMENT ON COLUMN adresse.plz_a IS 'Postleitzahl';
COMMENT ON COLUMN adresse.id_ort IS 'FOREIGN KEY: Ort';
COMMENT ON COLUMN adresse.id_land IS 'FOREIGN KEY: Land';
COMMENT ON CONSTRAINT fk_adresse1 ON adresse IS '
auch wenn ich den Ort zu einer Adresse nicht kennen so reicht doch
in der Regel auch die Postleitzahl, daher behalte ich erstmal alle
Adressen wenn der dazugehoerige Ort geloescht wird, setze aber
ort_id auf NULL. Dann kann allerdings die ort_id nicht zum
Schluessel dieser Tabelle gehoeren. Stattdessen nehme ich die plz.';
COMMENT ON CONSTRAINT fk_adresse2 ON adresse IS '
wird ein Land geloescht, so muß ich um inkonsistenzen zu vermeiden
auch alle dazu gespeicherten Adressen entfernen, da sonst genau wie
bei den Orten zwei gleiche Adressen in der DB stehen koennten.';
create unique index "adresse_ukey1" on adresse (strasse,h_nr,plz_a,id_land);
\i plpgsql_funcs/cr_func_adresse.sql
select public.start_logging('adresse', 'ALL');
set search_path to public;
-- ]
-- ende Adressen --
-- Tabellen für Personendaten
-- [
set search_path to person;
create table "person" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"anrede" varchar(10) NULL, -- Herr, Frau, Firma
"titel" text NULL, -- Dr., Prof., Dipl. Ing...
"nachname" text NOT NULL, -- Nachname
"vorname" text NOT NULL, -- Vorname
"geb_dat" date NOT NULL, -- Geburtsdatum
"postfach" varchar(10) NULL, -- Postfach
"telefon" varchar(10) NULL, -- Telefon persönlich
"fax" varchar(10) NULL, -- Fax persoenlich
"handy" varchar(50) NULL, -- Handy persoenlich
"email" varchar(50) NULL, -- E-Mail persoenlich
"webpage" varchar(100) NULL); -- Webseite persoenlich
COMMENT ON TABLE person IS 'Tabelle für Personendaten';
COMMENT ON COLUMN person._id_ IS 'interne id';
COMMENT ON COLUMN person.anrede IS 'Herr, Frau, Firma';
COMMENT ON COLUMN person.titel IS 'Dr., Prof., Dipl. Ing...';
COMMENT ON COLUMN person.nachname IS 'Nachname';
COMMENT ON COLUMN person.vorname IS 'Vorname';
COMMENT ON COLUMN person.geb_dat IS 'Geburtsdatum';
COMMENT ON COLUMN person.postfach IS 'Postfach';
COMMENT ON COLUMN person.telefon IS 'Telefon persönlich';
COMMENT ON COLUMN person.fax IS 'Fax persoenlich';
COMMENT ON COLUMN person.handy IS 'Handy persoenlich';
COMMENT ON COLUMN person.email IS 'E-Mail persoenlich';
COMMENT ON COLUMN person.webpage IS 'Webseite persoenlich';
create unique index "person_ukey1" on person (nachname, vorname, geb_dat);
select public.start_logging('person', 'ALL');
-- wohnt --
create table "wohnt" (
id_person int4 NOT NULL, -- Person
id_adresse int4 NOT NULL, -- Adresse
PRIMARY KEY (id_person, id_adresse),
FOREIGN KEY (id_person) REFERENCES person (_id_)
-- hier wird immer die ganze zuordnung geloescht, das ist ok, da
-- dadurch keinerlei weitere Daten verlohren gehen koennen.
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (id_adresse) REFERENCES adresse.adresse (_id_)
-- hier wird immer die ganze zuordnung geloescht, das ist ok, da
-- dadurch keinerlei weitere Daten verlohren gehen koennen.
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE wohnt IS 'Adresse zu Person';
COMMENT ON COLUMN wohnt.id_person IS 'Person';
COMMENT ON COLUMN wohnt.id_adresse IS 'Adresse';
\i plpgsql_funcs/cr_func_person.sql
select public.start_logging('wohnt', 'ALL');
set search_path to public;
-- ]
-- ende Personendaten
-- Arbeitsplatz --
-- [
set search_path to firma;
create table "firma" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"name" text NOT NULL, -- Name (Bezeichnung) der Firma
"beschreibung" text NULL, -- Was fuer eine Firma ist das
"plz_f" varchar(10) NULL); -- Postleitzahl (große Firme)
COMMENT ON TABLE firma IS 'Firmengrundinformation:
-------------------------------
meine Konzeption sieht vor das jede Firma mindestens eine Abteilung hat,
die alle Mitarbeiter umfasst wenn es keine weiteren Abteilungen gibt, gibt
es weitere Abteilungen koennen alle, einige oder keine Mitarbeiter zu dieser
Abteilung erfasst sein. Diese Abteilung heisst Firma.
Die Adresse zu dieser Abteilung ist immer die Adresse des Hauptsitzes der
Firma.';
COMMENT ON COLUMN firma._id_ IS 'interne id';
COMMENT ON COLUMN firma.name IS 'Name (Bezeichnung) der Firma';
COMMENT ON COLUMN firma.beschreibung IS 'Was fuer eine Firma ist das';
COMMENT ON COLUMN firma.plz_f IS 'Postleitzahl (große Firme)';
create unique index "firma_ukey1" on firma (name,plz_f);
select public.start_logging('firma', 'ALL');
create table "abteilung" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"bezeichnung" text NOT NULL, -- Name (Bezeichnung) der Abt.
"beschreibung" text NULL, -- was macht diese Abteilung
"telefon" varchar(10) NULL, -- Telefon (Zentrale)
"fax" varchar(10) NULL, -- Fax Abteilung
"email" varchar(50) NULL, -- E-Mail (gemeins. fuer Abtl.)
"postfach" varchar(10) NULL, -- Postfach
"id_firma" int4 NOT NULL, -- zu welcher Firma gehört sie
"id_adresse" int4 NULL, -- wo liegt sie.
CONSTRAINT fk_abteilung1
FOREIGN KEY (id_adresse) REFERENCES adresse.adresse (_id_)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT fk_abteilung2 FOREIGN KEY (id_firma) REFERENCES firma (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE abteilung IS 'Abteilungen zu Firmen
(siehe auch Kommentar zu table firma)
-------------------------------------
Bislang wird die Abteilung beim anlegen einer Firma in der function
ins_firma mit angelegt, besser waehre jedoch ein Trigger der bei jedem
insert in firma eine passende abteilung anlegt.';
COMMENT ON COLUMN abteilung._id_ IS 'interne id';
COMMENT ON COLUMN abteilung.bezeichnung IS 'Name (Bezeichnung) der Abt.';
COMMENT ON COLUMN abteilung.beschreibung IS 'was macht diese Abteilung';
COMMENT ON COLUMN abteilung.telefon IS 'Telefon (Zentrale)';
COMMENT ON COLUMN abteilung.fax IS 'Fax Abteilung';
COMMENT ON COLUMN abteilung.email IS 'E-Mail (gemeins. fuer Abtl.)';
COMMENT ON COLUMN abteilung.postfach IS 'Postfach';
COMMENT ON COLUMN abteilung.id_firma IS
'FOREIGN KEY: zu welcher Firma gehört sie';
COMMENT ON COLUMN abteilung.id_adresse IS 'FOREIGN KEY: wo liegt sie';
COMMENT ON CONSTRAINT fk_abteilung1 ON abteilung IS '
wird eine Adresse geloescht, so moechte ich unter umständen trotzdem
gerne alle weiteren Firmendaten halten. Daher setze ich in diesem
Fall id_adresse auf NULL.';
COMMENT ON CONSTRAINT fk_abteilung2 ON abteilung IS '
wird eine Firma gelöscht, so werden auch alle zu ihr gehoerenden
Abteilungen geloescht.';
create unique index "abteilung_ukey1" on abteilung (bezeichnung, id_firma);
select public.start_logging('abteilung', 'ALL');
create table "arbeit" (
"id_person" int4 NOT NULL, -- Person die arbeitet
"id_abteilung" int4 NOT NULL, -- Abteilung in der sie arbeitet
"position" text NOT NULL, -- Position innerhalb der Abt. o. Firma
"beschreibung" text NULL, -- Beschreibung der Taetigkeit
"telefon" varchar(10) NULL, -- Telefon beruflich
"fax" varchar(10) NULL, -- Fax beruflich
"email" varchar(50) NULL, -- E-Mail beruflich
"gehalt" numeric(10,2) NULL, -- Gehalt
PRIMARY KEY (id_person, id_abteilung),
CONSTRAINT fk_arbeit1
FOREIGN KEY (id_person) REFERENCES person.person (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_arbeit2 FOREIGN KEY (id_abteilung) REFERENCES abteilung (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE arbeit IS 'Person arbeitet in Abteilung';
COMMENT ON COLUMN arbeit.id_person IS 'FOREIGN KEY: Person die arbeitet';
COMMENT ON COLUMN arbeit.id_abteilung IS
'FOREIGN KEY: Abteilung in der sie arbeitet';
COMMENT ON COLUMN arbeit.position IS 'Position innerhalb der Abt. o. Firma';
COMMENT ON COLUMN arbeit.beschreibung IS 'Beschreibung der Taetigkeit';
COMMENT ON COLUMN arbeit.telefon IS 'Telefon beruflich';
COMMENT ON COLUMN arbeit.fax IS 'Fax beruflich';
COMMENT ON COLUMN arbeit.email IS 'E-Mail beruflich';
COMMENT ON COLUMN arbeit.gehalt IS 'Gehalt';
COMMENT ON CONSTRAINT fk_arbeit1 ON arbeit IS '
da es sich hier nur um eine Relation mit Zusatzinformationen
handelt ist ein loeschen ok, denn wenn die Person geloescht wird
sind auch die Zusatzinfos uninteressant.';
COMMENT ON CONSTRAINT fk_arbeit2 ON arbeit IS '
da es sich hier nur um eine Relation mit Zusatzinformationen
handelt ist ein loeschen ok, denn wenn die Abteilung geloescht wird
sind auch die Zusatzinfos uninteressant. Die Person ist dann
quasi arbeitslos in meiner DB.';
\i plpgsql_funcs/cr_func_arbeit.sql
select public.start_logging('arbeit', 'ALL');
set search_path to public;
-- ]
-- ende Arbeitsplatz --
-- Konto --
-- [
set search_path to fibu;
create table "konten_typen" (
"mask" bit varying NOT NULL PRIMARY KEY, -- interne id
"name" text NOT NULL, -- Bezeichnung des Kontentyps (AKTIVA)
"beschreibung" text NULL); -- eine Beschreibung wofuer der Typ ist
COMMENT ON TABLE konten_typen IS 'z.B. AKTIVA,AUWAND,EIGENKAPITAL etc.';
COMMENT ON COLUMN konten_typen.mask IS 'interne id';
COMMENT ON COLUMN konten_typen.name IS 'Bezeichnung des Kontentyps (AKTIVA)';
COMMENT ON COLUMN konten_typen.beschreibung IS
'eine Beschreibung wofuer der Typ ist';
create unique index "konten_typen_ukey1" on konten_typen (name);
select public.start_logging('konten_typen', 'ALL');
create table "used_konten" (
"kto_nr" int4 NOT NULL PRIMARY KEY, -- interne id
"used" int4 NOT NULL, -- von wievielen genutzt
"konto_typ" bit varying NOT NULL, -- kontentyp
CONSTRAINT fk_used_konten1
FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE used_konten IS '
Tabelle enthaelt alle zur verfuegung stehenden Kontonummern mit einem
Flag das anzeigt wie oft auf das Konto referenziert wird.';
COMMENT ON COLUMN used_konten.kto_nr IS 'interne id';
COMMENT ON COLUMN used_konten.used IS 'von wievielen genutzt';
COMMENT ON COLUMN used_konten.konto_typ IS 'FOREIGN KEY: kontentyp';
-- hier schenk ich mir das beim insert zugunsten einer besseren
-- laufzeit....spaeter bau cih moeglichkeiten ein, das logging seperat
-- fuer jedem table an und abzustellen und zwar separat fuer INSERT,UPDATE
-- und DELETE...
select public.start_logging('used_konten', 'UDPATE');
select public.start_logging('used_konten', 'DELETE');
create table "kontenplan" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"konto_typ" bit varying NOT NULL, -- Bestands-, Erfolgs- etc
"name" text NOT NULL, -- Bezeichnung des Kontenbereichs
"nr_von" int4 NOT NULL, -- Startwert der Kontonummern
"nr_bis" int4 NOT NULL, -- Wert von letzter gueltiger Nummer
CONSTRAINT fk_kontenplan1
FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE kontenplan IS 'Kontonummernbereiche zu Kontotypen:
------------------------------------
Hier gibt es ein wenig zu beachten damit die Tabelle konsistent bleibt
a) zu jedem Kontentyp darf nur ein Eintrag existieren.
(Das scheint mir schwachsinnig zu sein.....konto_typ,name muss unique
sein.)
b) Die Nummernbereiche duerfen sich nicht ueberschneiden
c) Zu jedem neuen Eintrag in kontenplan muessen entsprechende Eintraege
in used_konten gemacht werden. Und schliesslich
d) wird ein Eintrag in kontenplan erfolgreich geaender (d.h. es gibt
(keine Ueberschneidungen mit anderen Eintraegen, dann muessen die
entsprechenden Eintraege in used Konten angepasst werde, der Status darf
sich dort nicht aendern, wenn im neuen Bereich weniger Nummern existieren
muessen ueberzaehlige Eintraege entfernt werden (ACHTUNG: hierbei kann
es dann dazu kommen das auch Eintraege in Tabellen die Kontonummern
als Fremdschluessel enthalten geloescht werden.) oder wenn der neue
Bereich mehr Kontonummern enthaelt weitere Eintraege in used_konten
eingefuegt werden.
e) wird ein Eintrag geloescht so muessen auch alle dazu gehoerenden Eintraege
in used_konten geloescht werden (ACHTUNG: hierbei kann es dazu kommen,
dass auch Eintraege in Tabellen die Kontonummern als Fremdschluessel
enthalten geloescht werden. Dies betrifft insbesondete Debitoren,
Kreditoren und Warengruppen.)';
COMMENT ON COLUMN kontenplan._id_ IS 'interne id';
COMMENT ON COLUMN kontenplan.konto_typ IS
'FOREIGN KEY: Bestands-, Erfolgs- etc';
COMMENT ON COLUMN kontenplan.name IS 'Bezeichnung des Kontenbereichs';
COMMENT ON COLUMN kontenplan.nr_von IS 'Startwert der Kontonummern';
COMMENT ON COLUMN kontenplan.nr_bis IS 'Wert von letzter gueltiger Nummer';
-- zu kontenplan a)
create unique index "kontenplan_ukey1" on kontenplan (konto_typ, name);
COMMENT ON INDEX kontenplan_ukey1 IS 'siehe COMMENT zu TABLE koentenplan a)';
select public.start_logging('kontenplan', 'ALL');
create table "konto" (
"kto_nr" int4 NOT NULL PRIMARY KEY, -- Kontonummer, intern
"name" text NULL, -- Kontobezeichnung (Kasse etc.)
"konto_typ" bit varying NOT NULL, -- Typ (AKTIVA, DEBITOR...)
"saldo_soll" numeric(10,2) DEFAULT 0.0 NOT NULL, -- Saldo im soll
"saldo_haben" numeric(10,2) DEFAULT 0.0 NOT NULL, -- Saldo im haben
"summe" numeric(10,2) DEFAULT 0.0 NOT NULL, -- Gesamtsumme des Kontos
"blz" varchar(8) NULL, -- Bankleitzahl (falls vorhanden)
"b_kto_nr" text NULL, -- Bank-Kontonummer (falls vorhanden)
"b_name" text NULL, -- Name der Bank
CONSTRAINT fk_konto1
FOREIGN KEY (konto_typ) REFERENCES konten_typen (mask)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE konto IS 'Repraesentation eines Kontos fuer die Buchfuehrung';
COMMENT ON COLUMN konto.kto_nr IS 'Kontonummer, intern';
COMMENT ON COLUMN konto.name IS 'Kontobezeichnung (Kasse etc.)';
COMMENT ON COLUMN konto.konto_typ IS 'FOREIGN KEY: Typ (AKTIVA, DEBITOR...)';
COMMENT ON COLUMN konto.saldo_soll IS 'Saldo im soll';
COMMENT ON COLUMN konto.saldo_haben IS 'Saldo im haben';
COMMENT ON COLUMN konto.summe IS 'Gesamtsumme des Kontos';
COMMENT ON COLUMN konto.blz IS 'Bankleitzahl (falls vorhanden)';
COMMENT ON COLUMN konto.b_kto_nr IS 'Bank-Kontonummer (falls vorhanden)';
COMMENT ON COLUMN konto.b_name IS 'Name der Bank';
select public.start_logging('konto', 'ALL');
create table "buchung" (
"kto_soll" int4 NOT NULL, -- Buchung von Konto
"kto_haben" int4 NOT NULL, -- nach Konto
"be_nr" varchar(10) NOT NULL, -- Belegnummer
"bu_date" date NOT NULL, -- Buchungsdatum
"buchungstext" text NULL, -- Zusatzinformationen
"betrag" numeric(10,2) NOT NULL, -- Betrag der Buchung
PRIMARY KEY (be_nr, bu_date),
CONSTRAINT fk_buchung1 FOREIGN KEY (kto_soll) REFERENCES konto (kto_nr)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_buchung2 FOREIGN KEY (kto_haben) REFERENCES konto (kto_nr)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMENT ON TABLE buchung IS 'alle Buchungsaetze zwischen Konten:
------------------------------------
mehrteilige Buchungen werden als mehrere Buchungen zur gleichen Belegnummer
und dem gleichen Datum erfasst. Es soll auch moeglich sein einen Beleg an
mehreren Tagen zu Buchen. Daher die wahl des Primaerschluessels als
kombination von kto_soll, kto_haben, be_nr, bu_date.';
COMMENT ON COLUMN buchung.kto_soll IS 'FOREIGN KEY: Buchung von Konto';
COMMENT ON COLUMN buchung.kto_haben IS 'FOREIGN KEY: nach Konto';
COMMENT ON COLUMN buchung.be_nr IS 'Belegnummer';
COMMENT ON COLUMN buchung.bu_date IS 'Buchungsdatum';
COMMENT ON COLUMN buchung.buchungstext IS 'Zusatzinformationen';
COMMENT ON COLUMN buchung.betrag IS 'Betrag der Buchung';
-- alle Funktionen zu Konten
\i plpgsql_funcs/cr_func_konto.sql
select public.start_logging('buchung', 'ALL');
-- Rules um die Bitmasken einheitlich zu halten.
-- Dafuer hatte ich vorher einen Trigger, aber ich denke das ist sinnvoller
-- in Rules zu implementiern.
-- Das kann ich leider nicht machen, fuer inserts funktioniert das noch,
-- aber leider muessten updates-rules nach den rewrite entweder eine andere
-- action oder eine andere Zielrelation haben, beides kann ich nicht machne,
-- da ich nur den Wert veraendern will.
-- Daher bleibt mir nichts anderes uebrig als trigger zu nehmen.
set search_path to trigger_func;
\i trigger_funcs/cr_trigger_func_konto.sql
-- Trigger
-- Dieser Trigger hat bevor ich obige Rules hatte die Bitmasken in ordnung
-- gehalten, jetzt wird er nicht mehr gebraucht!
create trigger valid_mask_trigger_before
BEFORE INSERT OR UPDATE ON fibu.konten_typen FOR EACH ROW
EXECUTE PROCEDURE create_valid_mask_before('fibu.konten_typen');
create trigger valid_mask_trigger_after
AFTER INSERT OR UPDATE ON fibu.konten_typen FOR EACH ROW
EXECUTE PROCEDURE create_valid_mask_after('fibu.konten_typen');
COMMENT ON TRIGGER valid_mask_trigger_before ON fibu.konten_typen IS
'wird ein neuer Kontentyp eingefuegt oder aber ein Kontentyp geaendert '
'so muss sicher gestellt sein, das alle Typen die gleiche laenge haben, '
'da sich sonst keine logischen Operationen mit ihnen ausfuehren lassen.';
COMMENT ON TRIGGER valid_mask_trigger_after ON fibu.konten_typen IS
'wird ein neuer Kontentyp eingefuegt oder aber ein Kontentyp geaendert '
'so muss sicher gestellt sein, das alle Typen die gleiche laenge haben, '
'da sich sonst keine logischen Operationen mit ihnen ausfuehren lassen.';
create trigger fibu_trigger1_on_ref_count_null_del_konto
AFTER UPDATE ON fibu.used_konten FOR EACH ROW
EXECUTE PROCEDURE on_ref_count_null_del_konto();
COMMENT ON TRIGGER fibu_trigger1_on_ref_count_null_del_konto
ON fibu.used_konten IS
'Wenn niemand mehr auf das konto referenziert kann der entsprechende
Eintrag in der Tabelle konto geloescht werden.';
-- zu kontenplan b)
create trigger fibu_trigger1_on_change_check_ranges
BEFORE INSERT OR UPDATE ON fibu.kontenplan FOR EACH ROW
EXECUTE PROCEDURE on_change_kontenplan_check_ranges();
COMMENT ON TRIGGER fibu_trigger1_on_change_check_ranges
ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan b)';
-- zu kontenplan c)
create trigger fibu_trigger2_on_insert_create_used_konten
AFTER INSERT ON fibu.kontenplan FOR EACH ROW
EXECUTE PROCEDURE on_insert_kontenplan_create_used_konten();
COMMENT ON TRIGGER fibu_trigger2_on_insert_create_used_konten
ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan c)';
-- zu kontenplan d)
create trigger fibu_trigger3_on_update_update_used_konten
AFTER UPDATE ON fibu.kontenplan FOR EACH ROW
EXECUTE PROCEDURE on_update_kontenplan_upd_used_konten();
COMMENT ON TRIGGER fibu_trigger3_on_update_update_used_konten
ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan c)';
-- zu kontenplan e)
create trigger fibu_trigger4_on_delete_del_used_konten
AFTER DELETE ON fibu.kontenplan FOR EACH ROW
EXECUTE PROCEDURE on_delete_kontenplan_del_used_konten();
COMMENT ON TRIGGER fibu_trigger4_on_delete_del_used_konten
ON fibu.kontenplan IS 'siehe COMMENT zu TABLE koentenplan e)';
-- Trigger ende
set search_path to fibu;
insert into konten_typen (mask, name) values (B'1','BESTAND');
insert into konten_typen (mask, name) values (konto_typ('BESTAND',2)|
B'10', 'AKTIVA');
insert into konten_typen (mask, name) values (konto_typ('AKTIVA',3)|
B'100', 'ANLAGE');
insert into konten_typen (mask, name) values (konto_typ('AKTIVA',4)|
B'1000', 'UMLAUF');
insert into konten_typen (mask, name) values (konto_typ('UMLAUF',5)|
B'10000','DEBITOR');
insert into konten_typen (mask, name) values (konto_typ('BESTAND',6)|
B'100000','PASSIVA');
insert into konten_typen (mask, name) values (konto_typ('PASSIVA',7)|
B'1000000','EIGEN');
insert into konten_typen (mask, name) values (konto_typ('PASSIVA',8)|
B'10000000','FREMD');
insert into konten_typen (mask, name) values (konto_typ('FREMD',9)|
B'100000000','KREDITOR');
insert into konten_typen (mask, name) values (B'1000000000','ERFOLG');
insert into konten_typen (mask, name) values (konto_typ('ERFOLG',11)|
B'10000000000','ERTRAG');
insert into konten_typen (mask, name) values (konto_typ('ERFOLG',12)|
B'100000000000','AUFWAND');
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('ANLAGE',konto_typ_min_len()), 'Anlagevermögen', 100, 109);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('UMLAUF',konto_typ_min_len()),
'Umlaufvermögen ohne Forderungen LL', 110, 119);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('DEBITOR',konto_typ_min_len()), 'Forderungen LL', 1000, 1999);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('EIGEN',konto_typ_min_len()), 'Eigenkapital', 200, 209);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('FREMD',konto_typ_min_len()),
'Fremdkapital ohne Verbindlichkeiten LL', 210, 219);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('KREDITOR',konto_typ_min_len()),
'Verbindlichkeiten LL', 2000, 2999);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('ERTRAG',konto_typ_min_len()), 'Erträge', 3000, 3999);
insert into kontenplan (konto_typ, name, nr_von, nr_bis) values
(konto_typ('AUFWAND',konto_typ_min_len()), 'Aufwendungen', 4000, 4999);
select inc_konto_ref_count(
ins_konto(NULL, 'Hardware', konto_typ('ANLAGE',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Software', konto_typ('ANLAGE',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Kasse', konto_typ('UMLAUF',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Bankkonto', konto_typ('UMLAUF',konto_typ_min_len()),
'20110022', '3 003 938 178', 'Postbank (Spar) Hamburg'));
select inc_konto_ref_count(
ins_konto(NULL, 'Forderungen sonstige',
konto_typ('UMLAUF',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Eigenkapital', konto_typ('EIGEN',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Privatkonto', konto_typ('EIGEN',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Kredite', konto_typ('FREMD',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Verbindlichkeiten wiedekehrend',
konto_typ('FREMD',konto_typ_min_len()),
NULL, NULL, NULL));
select inc_konto_ref_count(
ins_konto(NULL, 'Verbindlichkeiten sonstige',
konto_typ('FREMD',konto_typ_min_len()),
NULL, NULL, NULL));
set search_path to public;
-- ]
-- ende Konto --
-- Benutzerdaten
-- [
set search_path to benutzer;
create table benutzer (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"user_name" text NOT NULL, -- Shop- und DB-Benutzername
-- Passwort brauchen wir nicht, wir werden ueber die DB authentifiziert.
--"password" text NOT NULL, -- Shop- und DB-Passwort
"id_person" int4 NULL, -- falls Person, sonst NULL
"id_firma" int4 NULL, -- falls Firma, sonst NULL
"u_typ" bit(3) NOT NULL DEFAULT B'001',-- 001-Kunde, 010-Anbieter, 100-Admin
CONSTRAINT fk_benutzer1
FOREIGN KEY (id_person) REFERENCES person.person (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_benutzer2
FOREIGN KEY (id_firma) REFERENCES firma.firma (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE benutzer IS '<U>username, passwort usw.:</U><BR>'
'bei id_kunde, id_admin, id_lieferant gilt dasselbe wie schon bei'
'den Kunden oder den Anbietern in etwas abgewandelter form. <BR>'
'Hier muss mindestens eines der Attribute NOT NULL sei, aber es duerfen '
'auch alle not NULL sein.<BR>'
'Zur Erinnerung, bei Kunden durfte immer nur entweder id_person oder '
'id_firma einen Wert haben, das jeweils andere Attribut musste NULL sein.';
COMMENT ON COLUMN benutzer._id_ IS 'interne id';
COMMENT ON COLUMN benutzer.user_name IS 'Shop- und DB-Benutzername';
--COMMENT ON COLUMN benutzer.password IS 'Shop- und DB-Passwort';
COMMENT ON COLUMN benutzer.id_person IS 'FOREIGN KEY: falls Person, sonst NULL';
COMMENT ON COLUMN benutzer.id_firma IS 'FOREIGN KEY: falls Firma, sonst NULL';
COMMENT ON COLUMN benutzer.u_typ IS '001-Kunde, 010-Anbieter, 100-Admin';
select public.start_logging('benutzer', 'ALL');
set search_path to trigger_func,benutzer;
\i trigger_funcs/cr_trigger_func_benutzer.sql
CREATE trigger benutzer_trigger1_on_change_one_null
BEFORE INSERT OR UPDATE ON benutzer.benutzer FOR EACH ROW
EXECUTE PROCEDURE on_change_one_null();
COMMENT ON TRIGGER benutzer_trigger1_on_change_one_null ON
benutzer.benutzer IS 'stellt sicher das immer nur entweder eine Firma '
'oder eine Person der Benutzer ist.';
-- OK, man koennte fragen warum eine Firma ein Benutzer sein sollte, aber
-- so kann ich zwischen Privat und fuer Firmen genutzten Benutzeraccounts
-- unterscheiden.
set search_path to benutzer;
create table rechte (
"mask" bit varying NOT NULL PRIMARY KEY, -- rechte-Maske
"name" text NOT NULL, -- Rechte-Name
"beschreibung" text NULL); -- Beschreibung
COMMENT ON TABLE rechte IS
'Alle Rechte die ein Admin haben kann mit den entsprechenden Masken\n'
'Hier werden nur grundlegende Rechtegebiete gespeichert. Diese sind\n'
'zur Zeit:<BR>\n'
'<UL>\n'
'<LI><B>benutzer</B> = 1 <I>Zugang zur Benutzerverwaltung</I></LI>\n'
'<LI><B>person</B> = 10 <I>Zugang zur Personendaten</I></LI>\n'
'<LI><B>firma</B> = 100 <I>Zugang zur Firmendaten</I></LI>\n'
'<LI><B>fibu</B> = 1000 <I>Zugang zur Shop-Fibu</I></LI>\n'
'<LI><B>warengruppe</B> = 10000 <I>Zugang zu WGs aller Anbieter</I></LI>\n'
'<LI><B>produkte</B> = 100000 <I>Zugang zu Prod aller Anbieter</I></LI>\n'
'<LI><B>rechte</B> = 1000000 <I>Zugang zu Rechtesystem</I></LI>\n'
'</UL>\n'
'<BR>Um zu sehen welche Rechte ein admin in einem bestimmten Bereich hat\n'
'gibt es die Tabelle effektive_rechte. (siehe Beschreibung dort)<BR>\n'
'Gehoeren mehrere Tabellen zu den Gebiete, so hat der Admin die gesetzten\n'
'Rechte an allen Tabellen dieses Gebites.<BR>\n'
'Die im Moment eingetragenen Rechte betreffen hauptsaechlich\n'
'Datenbankzugriffe, man koennte sich vorstellen weiter Rechte wie z.B.\n'
'Newsletteradmin einzutragen. Bei diesen wuerde dann entweder gar kein\n'
'Eintrag in effektive Rechte noetig sein, oder aber nur das feld spicial\n'
'ist wichtig.';
COMMENT ON COLUMN rechte.mask IS 'rechte-Maske';
COMMENT ON COLUMN rechte.name IS 'Rechte-Name';
COMMENT ON COLUMN rechte.beschreibung IS 'Beschreibung';
select public.start_logging('rechte', 'ALL');
set search_path to trigger_func,benutzer;
\i trigger_funcs/cr_trigger_func_rechte.sql
-- Hier muss genau wie bei den Kontentypen darauf geachtet werden das die
-- Bitmasken immer eine einheitlich laenge haben.
create trigger valid_mask_trigger_before
BEFORE INSERT OR UPDATE ON benutzer.rechte FOR EACH ROW
EXECUTE PROCEDURE create_valid_mask_before('benutzer.rechte');
create trigger valid_mask_trigger_after
AFTER INSERT OR UPDATE ON benutzer.rechte FOR EACH ROW
EXECUTE PROCEDURE create_valid_mask_after('benutzer.rechte');
set search_path to benutzer;
insert into rechte (mask, name, beschreibung) values
(B'1', 'user', 'Zugang zur Benutzerverwaltung');
insert into rechte (mask, name, beschreibung) values
(B'10', 'person', 'Zugang zur Personendaten');
insert into rechte (mask, name, beschreibung) values
(B'100', 'firma' , 'Zugang zur Firmendaten');
insert into rechte (mask, name, beschreibung) values
(B'1000', 'fibu' , 'Zugang zur Shop-Fibu');
insert into rechte (mask, name, beschreibung) values
(B'10000', 'warengruppe', 'Zugang zu WGs aller Anbieter');
insert into rechte (mask, name, beschreibung) values
(B'100000', 'produkt', 'Zugang zu Prod aller Anbieter');
insert into rechte (mask, name, beschreibung) values
(B'1000000', 'rules', 'Zugang zu Rechtesystem');
--CREATE trigger rechte_trigger2_on_chg_or_del_clean_effektive_rechte
-- BEFORE UPDATE OR DELETE ON benutzer.rechte FOR EACH ROW
-- EXECUTE PROCEDURE clean_effektive_rechte();
-- Stattdessen bietet sich eine rule an, da ich nicht viel Logic brauche
-- sondern einfach nur nen update auf effektive rechte mache. Etwa so...
-- erstmal ne Rule um die Breite der mask konsistent zu halten....
--CREATE RULE clean_effektive_rechte_1 AS
-- ON UPDATE OR DELETE TO rechte
-- DO ( UPDATE effektive_rechte SET
create table admin (
"id_benutzer" int4 NOT NULL PRIMARY KEY, -- Benutzer des Admin
"rechte" bit varying DEFAULT B'0' NOT NULL, -- kombination von RechteMasken
CONSTRAINT fk_admin1
FOREIGN KEY (id_benutzer) REFERENCES benutzer (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE admin IS
'<U>Hier werden alle Benutzer die Admins sind mit den passenden Attributen '
' gespeichert:</U><BR><BR>'
'zwar ist rechte kein FOREIGN KEY auf rechte, aber trotzdem muss'
'ueber einen Trigger sichergestellt werden das wenn Rechte geloescht'
'oder geandert werden die Masken hier angepasst werden.'
'Beim loeschen ist das recht einfach, man loescht einfach bei allen'
'Admins das entsprechende bit in ihrer Maske, schwere ist das wenn'
'ein Recht in rechte geaendert wird, dann habe ich quasi keine chance'
'noch zu checke ob Admins die das entsprechende Bit gesetzt haben immer'
'noch die passenden rechte haben. Die Loesung hier ist ueber einen'
'Trigger alle UPDATES auf Rechte abzuschmettern.';
COMMENT ON COLUMN admin.rechte IS 'kombination von RechteMasken';
COMMENT ON COLUMN admin.id_benutzer IS 'FOREIGN KEY: Benutzerdaten des Admin';
select public.start_logging('admin', 'ALL');
create table effektive_rechte (
id_admin int4 NOT NULL, -- Id des Admin zu dem die Rechte gehoeren.
mask bit varying NOT NULL, -- Maske ueber die Rechte fuer die diese
-- Enstellung gelten soll, diese darf sich
-- nicht mit anderen recht_masken zu einer
-- id_admin ueberschneiden, (Trigger).
-- ausserdem muss zu allen Bits dieser Maske
-- auch ein Eintrag in rechte vorhanden sein,
-- (ein weitere Trigger, oder sogar mehrere,
-- einer beim update, einfuegen hier und einer
-- beim update, loeschen in rechte)
r_add boolean NOT NULL DEFAULT 'f', -- eintraege hinzufuegen
r_delete boolean NOT NULL DEFAULT 'f', -- eintraege loeschen
r_update boolean NOT NULL DEFAULT 'f', -- eintraege aktualisieren
special bit varying NULL, -- spezielle Rechte
PRIMARY KEY (id_admin, mask),
CONSTRAINT fk_effektive_rechte
FOREIGN KEY (id_admin) REFERENCES admin (id_benutzer)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE effektive_rechte IS
'Diese Tablle dient dazu genau zu beschreiben was ein admin in einem\n'
'der Gebiete (aus rechte) fuer die er Eingetragen ist fuer Rechte hat.\n'
'insbesondere, ob er adden, deleten und/oder updaten darf.<BR>\n'
'Die hier eingetragene recht_maske kann mehrere Rechte umfassen (im\n'
'folgenden Text Rechtegruppe genannt), dann gelten die Einstellungen\n'
'fuer alle Rechte dieser Rechtegruppe. Wird die Einstellung fuer ein\n'
'Recht oder eine Rechtegruppe nachtraeglich geaendert, so wird zuerst\n'
'nachgesehen ob bereits Eintraege zu diesen Rechten vorhanden sind.\n'
'Diese werden aus den Rechtegruppen in denen sie waren ausgekoppelt und\n'
'als Einzeleintraege neu eingetragen. Dabei werden dann auch gleich die\n'
'zu aendernden Attribute neu gesetzt.<BR>\n'
'Das Feld special dient zum speichern etwaiger besonderer Rechte, z.B.\n'
'Wenn eine Tabelle des Gebiets nur von bestimmten Admins geandert werden\n'
'darf kann hier nochmal ein Bit gesetzt werden das eine solche\n'
'Unterscheidung moeglich macht.';
COMMENT ON COLUMN effektive_rechte.id_admin IS
'Id des Admin zu dem die Rechte gehoeren.';
COMMENT ON COLUMN effektive_rechte.mask IS
'Maske ueber die Rechte fuer die diese Enstellung gelten soll, diese\n'
'darf sich nicht mit anderen recht_masken zu einer id_admin\n'
'ueberschneiden, (Trigger).<BR>\n'
'ausserdem muss zu allen Bits dieser Maske auch ein Eintrag in rechte\n'
'vorhanden sein, (ein weitere Trigger, oder sogar mehrere, einer beim\n'
'update, einfuegen hier und einer beim update, loeschen in rechte)';
COMMENT ON COLUMN effektive_rechte.r_add IS 'eintraege hinzufuegen';
COMMENT ON COLUMN effektive_rechte.r_delete IS 'eintraege loeschen';
COMMENT ON COLUMN effektive_rechte.r_update IS 'eintraege aktualisieren';
COMMENT ON COLUMN effektive_rechte.special IS 'spezielle Rechte';
select public.start_logging('effektive_rechte', 'ALL');
-- hier fehlt noch fast alles...is nur schon mal da das ichs nich vergesse :-)
-- is die frage ob ich das ueberhaupt brauch wenn ich das session management
-- von JSP/struts oder PHP nutze....sprich das SessionManagement programmatisch
-- umsetze...
create table session (
sess_id text NOT NULL PRIMARY KEY);
COMMENT ON TABLE session IS
'Hier werden alle Information zu Sitzungen am Shop gespeichert...'
'es fehlt aber quasi noch alles!';
create table "kunde" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"kd_nr" text NOT NULL, -- Kundennummer
"konto1" int4 NOT NULL, -- Debitor-Konto (Rechnung)
"konto2" int4 NOT NULL, -- Kreditor-Konto (Vorkasse)
"id_benutzer" int4 NOT NULL, -- Benutzerdaten des Kunden
CONSTRAINT fk_kunde1
FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_kunde2
FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_kunde3
FOREIGN KEY (id_benutzer) REFERENCES benutzer.benutzer (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE kunde IS 'Kundeninformationen:
----------------------------
Hier gibt es wieder was zu beachten und zu automatisieren.
a) beim einfuegen eines kunden muss,
a.a) wenn kontonummern mit angegeben werden, geprueft werden ob diese
wirklich den richtigen Typ haben, ob sie ueberhaupt schon in Konto
engetragen sind, wenn nicht muß dies passieren vorausgesetzt der Typ
stimmt, und in used_konten muss used auf true gesetzt werden, dies
kann ruhig auch gemacht werden wenn die Konten bereits genutzt
wurden.
a.b) wenn keine kontonummern mit angegeben werden muessen automatisch
passende Konten angelegt werden und die Nummern eingesetzt werden.
Auch hier muss natuerlich used_konten angepasst werden.
b) beim aendern eines kunden muss,
b.a) wenn kontonummern mit angegeben werden, muessen diese wie beim
einfuegen kontrolliert werden bevor sie eingefuegt werden.
ausserdem muß der ref_count bei den alten konten runter und bei
den neuen rauf gesetzt werden. (Das laeuft darauf hinaus das ich
einmal ins_konto mache, das gibt mir die Kontonummer zurueck wenn
das Konto schon existierte oder wenn es angelegt wurde und ich
mich dann um die ref_counts kuemmere.)
b.b) wenn keine kontonummern mit angegeben werden, die alten beibehalten,
also in der Triggerfunc wieder auf die neuen uebertragen werden.
c) beim loeschen eines kunden, sofern keine anderer Kunde oder Anbieter
mehr das Konto nutzt kann es geloescht werden und used in used_konten
wieder auf false gesetzt werden. (OK, hier kommt gleich eine Schwaeche
des aktuellen System zum vorschein, naemlich das man nicht leicht
feststellen kann ob das Konto noch genutzt wird....abhilfe schaffe ich
indem ich used in used_konten von boolean auf int4 umstelle und definiere
0 ist ungenutzt und sobald einer das konto nutzt wird used um 1
hochgezaehlt, nutzt einer es nicht mehr wird used um 1 runtergezaehlt.
(ist gemacht, Kommentar loeschen sobald es getestet ist!)';
COMMENT ON COLUMN kunde._id_ IS 'interne id';
COMMENT ON COLUMN kunde.kd_nr IS 'Kundennummer';
COMMENT ON COLUMN kunde.konto1 IS 'FOREIGN KEY: Debitor-Konto (Rechnung)';
COMMENT ON COLUMN kunde.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Vorkasse)';
COMMENT ON COLUMN kunde.id_benutzer IS 'FOREIGN KEY: Benutzerdaten';
COMMENT ON CONSTRAINT fk_kunde1 ON kunde IS
'wird ein Konto aus used_konten geloescht oder dessen _id_ veraendert,
so soll eine entsprechende anpassung hier stattfinden, das gilt auch
fuer alle weiteren Tabellen die Konten nutzen.
ob es sinnvoll ist einen Kunden zu loeschen wenn ein entsprechnder
Eintrag in used_konten geloescht wird muss nochmal ueberdacht werden.';
COMMENT ON CONSTRAINT fk_kunde2 ON kunde IS
'wird ein Konto aus used_konten geloescht oder dessen _id_ veraendert,
so soll eine entsprechende anpassung hier stattfinden, das gilt auch
fuer alle weiteren Tabellen die Konten nutzen.
ob es sinnvoll ist einen Kunden zu loeschen wenn ein entsprechnder
Eintrag in used_konten geloescht wird muss nochmal ueberdacht werden.';
create unique index "kunde_ukey" on kunde (kd_nr);
\i plpgsql_funcs/cr_func_kunde.sql
select public.start_logging('kunde', 'ALL');
set search_path to trigger_func,benutzer;
-- Trigger
CREATE trigger kunde_trigger2_on_insert_create_konten
BEFORE INSERT ON benutzer.kunde FOR EACH ROW
EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER kunde_trigger2_on_insert_create_konten
ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde a)';
CREATE trigger kunde_trigger3_on_update_update_konten
BEFORE UPDATE ON benutzer.kunde FOR EACH ROW
EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER kunde_trigger3_on_update_update_konten
ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde b)';
CREATE trigger kunde_trigger4_on_delete_dec_konto_ref_count
AFTER DELETE ON benutzer.kunde FOR EACH ROW
EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER kunde_trigger4_on_delete_dec_konto_ref_count
ON benutzer.kunde IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
set search_path to benutzer;
create table "anbieter" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"an_nr" text NOT NULL, -- Anbieternummer
"konto1" int4 NOT NULL, -- Debitor-Konto (Vorkasse)
"konto2" int4 NOT NULL, -- Kreditor-Konto (Rechnung)
"id_benutzer" int4 NOT NULL, -- Benutzerdaten oder NULL
CONSTRAINT fk_anbieter1
FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_anbieter2
FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_anbieter3
FOREIGN KEY (id_benutzer) REFERENCES benutzer.benutzer (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE anbieter IS
'Leute die Waren in meinem Shop anbieten:<BR>';
COMMENT ON COLUMN anbieter._id_ IS 'interne id';
COMMENT ON COLUMN anbieter.an_nr IS 'Anbieternummer';
COMMENT ON COLUMN anbieter.konto1 IS 'FOREIGN KEY: Debitor-Konto (Vorkasse)';
COMMENT ON COLUMN anbieter.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Rechnung)';
COMMENT ON COLUMN anbieter.id_benutzer IS 'FOREIGN KEY: Benutzerdaten:';
COMMENT ON CONSTRAINT fk_anbieter1 ON anbieter IS
'siehe COMMENT zu CONSTRAINT fk_kunde1';
COMMENT ON CONSTRAINT fk_anbieter2 ON anbieter IS
'siehe COMMENT zu CONSTRAINT fk_kunde2';
create unique index "anbieter_ukey" on anbieter (an_nr);
select public.start_logging('anbieter', 'ALL');
set search_path to trigger_func,benutzer;
-- Trigger
CREATE trigger anbieter_trigger2_on_insert_create_konten
BEFORE INSERT ON benutzer.anbieter FOR EACH ROW
EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten
ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde a)';
CREATE trigger anbieter_trigger3_on_update_update_konten
BEFORE UPDATE ON benutzer.anbieter FOR EACH ROW
EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten
ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde b)';
CREATE trigger anbieter_trigger4_on_delete_dec_konto_ref_count
AFTER DELETE ON benutzer.anbieter FOR EACH ROW
EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER anbieter_trigger2_on_insert_create_konten
ON benutzer.anbieter IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
set search_path to public;
-- ]
-- ende Benutzerdaten
-- Verkaueferdaten, Leute bei denen ich einkaufe, gehoert zu fibu.
-- [
set search_path to fibu;
create table "verkaeufer" (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"vk_nr" text NOT NULL, -- Anbieternummer
"konto1" int4 NOT NULL, -- Debitor-Konto (Vorkasse)
"konto2" int4 NOT NULL, -- Kreditor-Konto (Rechnung)
"id_person" int4 NULL, -- falls Person, sonst NULL
"id_firma" int4 NULL, -- falls Firma, sonst NULL
CONSTRAINT fk_verkaeufer1
FOREIGN KEY (konto1) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_verkaeufer2
FOREIGN KEY (konto2) REFERENCES fibu.used_konten (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_verkaeufer3
FOREIGN KEY (id_person) REFERENCES person.person (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_verkaeufer4
FOREIGN KEY (id_firma) REFERENCES firma.firma (_id_)
ON DELETE CASCADE
ON UPDATE CASCADE);
COMMENT ON TABLE verkaeufer IS 'Leute oder Firmen bei denen ich einkaufe.';
COMMENT ON COLUMN verkaeufer._id_ IS 'interne id';
COMMENT ON COLUMN verkaeufer.vk_nr IS 'Verkaeufernummer';
COMMENT ON COLUMN verkaeufer.konto1 IS 'FOREIGN KEY: Debitor-Konto (Vorkasse)';
COMMENT ON COLUMN verkaeufer.konto2 IS 'FOREIGN KEY: Kreditor-Konto (Rechnung)';
COMMENT ON COLUMN verkaeufer.id_person IS
'FOREIGN KEY: falls Person, sonst NULL';
COMMENT ON COLUMN verkaeufer.id_firma IS 'FOREIGN KEY: falls Firma, sonst NULL';
COMMENT ON CONSTRAINT fk_verkaeufer1 ON verkaeufer IS
'siehe COMMENT zu CONSTRAINT fk_kunde1';
COMMENT ON CONSTRAINT fk_verkaeufer1 ON verkaeufer IS
'siehe COMMENT zu CONSTRAINT fk_kunde2';
create unique index "verkaufer_ukey" on verkaeufer (vk_nr);
select public.start_logging('verkaeufer', 'ALL');
set search_path to trigger_func,fibu;
-- Trigger
CREATE trigger verkaeufer_trigger1_on_change_one_null
BEFORE INSERT OR UPDATE ON fibu.verkaeufer FOR EACH ROW
EXECUTE PROCEDURE on_change_one_null();
COMMENT ON TRIGGER verkaeufer_trigger1_on_change_one_null ON
fibu.verkaeufer IS 'stellt sicher das immer nur entweder eine Firma '
'oder eine Person der Varkaeufer ist.';
CREATE trigger verkaeufer_trigger2_on_insert_create_konten
BEFORE INSERT ON fibu.verkaeufer FOR EACH ROW
EXECUTE PROCEDURE on_insert_create_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten
ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde a)';
CREATE trigger verkaeufer_trigger3_on_update_update_konten
BEFORE UPDATE ON fibu.verkaeufer FOR EACH ROW
EXECUTE PROCEDURE on_update_update_konten('DEBITOR','KREDITOR');
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten
ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde b)';
CREATE trigger verkaeufer_trigger4_on_delete_dec_konto_ref_count
AFTER DELETE ON fibu.verkaeufer FOR EACH ROW
EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER verkaeufer_trigger2_on_insert_create_konten
ON fibu.verkaeufer IS 'siehe COMMENT zu TABLE kunde c)';
-- Ende Trigger
set search_path to public;
-- ]
-- ende Benutzerdaten
-- Table zu Waren, Warengruppen u. Lager --
-- [
set search_path to waren;
create table warengruppe (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"name" text NOT NULL, -- Bezeichnung der Warengruppe
"steuerkl" int4 NOT NULL, -- Steuern zu dieser Produktgruppe
"kurzbeschreibung" text NULL, -- Kurzbeschreibung
"beschreibung" text NULL, -- Beschreibung
"produkt_typen" bit varying NOT NULL, -- Maske der anzuzeigenden Produkte.
"pic_path" text NULL, -- optionaler Pfad zu einem Bild
"tiefe" int4 DEFAULT 0 NOT NULL, -- die wievielte Untergruppe
"parent" int4 NULL, -- uebergeordnete Warengruppe
"is_parent" boolean DEFAULT false NOT NULL, -- ist WG ein Parent??
"konto1" int4 NOT NULL, -- Wareneingangskonto
"konto2" int4 NOT NULL, -- Warenausgangskonto
-- produkt_typen ist kein FOREIGN KEY, da es sich hierbei um eine
-- Kombination von mehreren Typen aus der Tabelle produkt_typen handeln
-- kann.
CONSTRAINT fk_warengruppe1
FOREIGN KEY (konto1) REFERENCES fibu.konto (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_warengruppe2
FOREIGN KEY (konto2) REFERENCES fibu.konto (kto_nr)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT fk_warengruppe3
FOREIGN KEY (parent) REFERENCES warengruppe (_id_)
ON UPDATE CASCADE);
COMMENT ON TABLE warengruppe IS
'<U>Warengruppen unter denen Produkte oder weitere Warengruppen '
'organisiert werden:</U><BR><BR>'
'Enthaelt eine Warengruppe weitere Warengruppen, so sind die zu den '
'Untergruppen gehoerenden Eingangs und Ausgangskonten auch Unterkonten '
'zu den in der Obergruppe existierenden Konten, d.h. der Saldo der '
'Unterkonten muss immer auf das Oberkonto uebertragen werden. '
'Ich glaube dafuer sollte ich trigger bauen....<BR>'
'Die Konten sind natuerlich wie bei kunden automatisch anzulegen.';
COMMENT ON COLUMN warengruppe._id_ IS 'interne id';
COMMENT ON COLUMN warengruppe.name IS 'Bezeichnung der Warengruppe';
COMMENT ON COLUMN warengruppe.steuerkl IS 'Steuern zu dieser Produktgruppe';
COMMENT ON COLUMN warengruppe.kurzbeschreibung IS 'Kurzbeschreibung';
COMMENT ON COLUMN warengruppe.beschreibung IS 'Beschreibung';
COMMENT ON COLUMN warengruppe.produkt_typen IS
'Maske der anzuzeigenden Produkte.';
COMMENT ON COLUMN warengruppe.pic_path IS 'optionaler Pfad zu einem Bild';
COMMENT ON COLUMN warengruppe.tiefe IS 'die wievielte Untergruppe';
COMMENT ON COLUMN warengruppe.parent IS 'uebergeordnete Warengruppe';
COMMENT ON COLUMN warengruppe.is_parent IS 'ist WG ein Parent??';
COMMENT ON COLUMN warengruppe.konto1 IS 'Wareneingangskonto';
COMMENT ON COLUMN warengruppe.konto2 IS 'Warenausgangskonto';
create unique index "warengruppe_ukey1" on warengruppe(name);
\i plpgsql_funcs/cr_func_waren.sql
select public.start_logging('warengruppe', 'ALL');
set search_path to trigger_func,waren;
\i trigger_funcs/cr_trigger_func_waren.sql
-- Trigger
create trigger waren_trigger1_on_insert_create_konten
BEFORE INSERT ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_insert_create_konten('AUFWAND','ERTRAG');
COMMENT ON TRIGGER waren_trigger1_on_insert_create_konten
ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';
CREATE trigger waren_trigger2_on_update_update_konten
BEFORE UPDATE ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_update_update_konten('AUFWAND','ERTRAG');
COMMENT ON TRIGGER waren_trigger2_on_update_update_konten
ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';
CREATE trigger waren_trigger3_on_delete_dec_konto_ref_count
AFTER DELETE ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_delete_dec_konto_ref_count();
COMMENT ON TRIGGER waren_trigger3_on_delete_dec_konto_ref_count
ON waren.warengruppe IS 'siehe COMMENT zu TABLE kunde c)';
create trigger waren_trigger4_on_insert_set_tiefe
BEFORE INSERT ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE set_tiefe();
COMMENT ON TRIGGER waren_trigger4_on_insert_set_tiefe
ON waren.warengruppe IS 'setze tiefe auf parent.tiefe+1 oder 0 '
'wenn es eine Rootgruppe ist';
create trigger waren_trigger5_on_update_check_tiefe
BEFORE UPDATE ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_update_check_tiefe();
COMMENT ON TRIGGER waren_trigger5_on_update_check_tiefe
ON waren.warengruppe IS 'wenn sich parent aendert setze tiefe=neuer '
'parent.tiefe+1 oder 0 wenn der neue parent '
'NULL ist (Rootgruppe)';
create trigger waren_trigger6_on_delete_new_parent
BEFORE DELETE ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_delete_new_parent();
COMMENT ON TRIGGER waren_trigger6_on_delete_new_parent
ON waren.warengruppe IS 'wenn eine Warengruppe geloescht wird muss parent '
'bei allen untergeordneten Gruppen auf parent der '
'geloeschten Obergruppe gesetzt werden.';
create trigger waren_trigger7_on_update_delete_check_is_parent
AFTER UPDATE OR DELETE ON waren.warengruppe FOR EACH ROW
EXECUTE PROCEDURE on_update_delete_check_is_parent();
COMMENT ON TRIGGER waren_trigger7_on_update_delete_check_is_parent
ON waren.warengruppe IS 'nach update oder delete muss gecheckt werden ob '
'is_parent noch bei allen Eintraegen stimmt.';
-- Ende Trigger
set search_path to waren;
create table produkt_typen (
"produkt_typ" bit varying NOT NULL PRIMARY KEY, -- interne id
"bezeichnung" text NOT NULL, -- Bezeichnung, z.B. Kleidung, Uhren
"typ_table" text NULL); -- Tabellenname fuer Typinfos
COMMENT ON TABLE produkt_typen IS
'<U>Enthaelt Bitmasken zu verschiedenen Produkttypen:</u></br>'
'Eine Auswahl dieser Masken wird und-Verknuepft zu jedem Produkt und '
'zu jeder Warengruppe gespeichert. Ergibt eine oder-Verknuepfung der '
'Warengruppen-Maske und der Produkt-Maske einen Wert <> 0, so wird das '
'Produkt in der Warengruppe angezeigt.';
select public.start_logging('produkt_typen', 'ALL');
set search_path to trigger_func,waren;
CREATE TRIGGER waren_trigger1_create_valid_typ_mask
BEFORE INSERT OR UPDATE ON waren.produkt_typen FOR EACH ROW
EXECUTE PROCEDURE create_valid_produkt_typ_mask();
set search_path to waren;
CREATE TABLE typ_table_columns (
"produkt_typ" bit varying NOT NULL, -- produkt-typ
"col_name" text NOT NULL, -- Name der Zusatzcolumn
"col_type" text NOT NULL, -- Typ der Zusatzcolumn
PRIMARY KEY (produkt_typ, col_name),
CONSTRAINT fk_typ_table_columns
FOREIGN KEY (produkt_typ) REFERENCES produkt_typen (produkt_typ)
ON UPDATE CASCADE
ON DELETE CASCADE);
COMMENT ON TABLE typ_table_columns IS
'In dieser Tabelle findet man die definition der Zusatzdatenfelder zu '
'einem typ_table in produkt_typen.';
COMMENT ON COLUMN typ_table_columns.produkt_typ IS 'produkt-typ';
COMMENT ON COLUMN typ_table_columns.col_name IS 'Name der Zusatzcolumn';
COMMENT ON COLUMN typ_table_columns.col_type IS 'Typ der Zusatzcolumn';
select public.start_logging('typ_table_columns', 'ALL');
-- produkt ist eine allgemeine Oberklassifizierung von Produkten. Es enthaelt
-- alle Atribute, die alle Produkte gemeinsam haben.
-- Ausserdem enthaelt es eine Verknuepfung in die Tabelle produkt_typen, in
-- der etwaige Tabellen mit Zusatzinformationen zu bestimmten Produkten
-- aufgelistet sind. Effektiv bedeutet das auch das der Admin und auch
-- der Anzeigenbereich verschiedenen Anzeige und Eingabemasken fuer die
-- verschiedenen Produkte zur verfuegung stellen muss.
-- Das Backend liefert eine kombinierte Tabelle aus Produkt und der Speziellen
-- Produktabelle, aus der auch hervorgeht um welchen Produktyp es sich handelt.
create table produkt (
"_id_" SERIAL NOT NULL PRIMARY KEY, -- interne id
"name" text NOT NULL, -- Bezeichnung des Produktes
"kurzbeschreibung" text NULL, -- Kruzbeschreibung
"beschreibung" text NULL, -- Beschreibung
"small_pic_path" text NULL, -- optionaler Pfad zu einem kleinen Bild
"big_pic_path" text NULL, -- optionaler Pfad zu einem grossen Bild
"preis" numeric(10,2) NOT NULL, -- Produktpreis (netto)
"stueck" int4 DEFAULT 0 NOT NULL, -- verfuegbare Stueckmenge
-- ACHTUNG: Das produkt wird in allen Warengruppen angezeigt, bei denen
-- midestens ein Bit der Typ-Maske der Warengruppe mit einem Bit der
-- Typ-Maske des Produkts uebereinstimmt.
"produkt_typen" bit varying NULL); -- Produkttyp
select public.start_logging('produkt', 'ALL');
set search_path to public;
-- ]
-- Ende Waren, Warengruppen u. Lager --
-- View auf Adressdaten --
-- [
set search_path to adresse;
create view "adresse_v" (strasse,h_nr,plz_a,ort,vorwahl_o,
land,lnd_kz,vorwahl_l) as
select a.strasse, a.h_nr, a.plz_a, b.ort, b.vorwahl_o,
c.land, c.lnd_kz, c.vorwahl_l from
adresse.adresse a, adresse.ort b, adresse.land c where
a.id_ort = b._id_ and
b.id_land = c._id_;
set search_path to public;
-- ]
-- ende view --
-- View auf Personendaten --
-- [
set search_path to person;
create view "person_v" (anrede,titel,nachname,vorname,geb_dat,age,
postfach, telefon,fax,handy,email,webpage,
strasse,h_nr,plz_a,ort,vorwahl_o,
land,lnd_kz,vorwahl_l) as
select a.anrede, a.titel, a. nachname, a.vorname, a.geb_dat,
age(a.geb_dat), a.postfach, a.telefon, a.fax, a.handy,
a.email, a.webpage, b.strasse, b.h_nr, b.plz_a, c.ort,
c.vorwahl_o, d.land, d.lnd_kz, d.vorwahl_l from
person.person a, person.wohnt ab,
adresse.adresse b, adresse.ort c, adresse.land d where
a._id_ = ab.id_person and
b._id_ = ab.id_adresse and
b.id_ort = c._id_ and
c.id_land = d._id_;
grant SELECT on person.person_v TO GROUP guest;
set search_path to public;
-- ]
-- ende view --
commit;