8 create unique index Room_rno on Room using btree (roomno bpchar_ops);
15 create unique index WSlot_name on WSlot using btree (slotname bpchar_ops);
20 create unique index PField_name on PField using btree (name text_ops);
27 create unique index PSlot_name on PSlot using btree (slotname bpchar_ops);
34 create unique index PLine_name on PLine using btree (slotname bpchar_ops);
40 create unique index Hub_name on Hub using btree (name bpchar_ops);
47 create unique index HSlot_name on HSlot using btree (slotname bpchar_ops);
48 create index HSlot_hubname on HSlot using btree (hubname bpchar_ops);
53 create unique index System_name on System using btree (name text_ops);
60 create unique index IFace_name on IFace using btree (slotname bpchar_ops);
66 create unique index PHone_name on PHone using btree (slotname bpchar_ops);
67 -- ************************************************************
69 -- * Trigger procedures and functions for the patchfield
72 -- ************************************************************
73 -- ************************************************************
74 -- * AFTER UPDATE on Room
75 -- * - If room no changes let wall slots follow
76 -- ************************************************************
77 create function tg_room_au() returns opaque as '
79 if new.roomno != old.roomno then
80 update WSlot set roomno = new.roomno where roomno = old.roomno;
85 create trigger tg_room_au after update
86 on Room for each row execute procedure tg_room_au();
87 -- ************************************************************
88 -- * AFTER DELETE on Room
89 -- * - delete wall slots in this room
90 -- ************************************************************
91 create function tg_room_ad() returns opaque as '
93 delete from WSlot where roomno = old.roomno;
97 create trigger tg_room_ad after delete
98 on Room for each row execute procedure tg_room_ad();
99 -- ************************************************************
100 -- * BEFORE INSERT or UPDATE on WSlot
101 -- * - Check that room exists
102 -- ************************************************************
103 create function tg_wslot_biu() returns opaque as '
105 if count(*) = 0 from Room where roomno = new.roomno then
106 raise exception ''Room % does not exist'', new.roomno;
110 ' language 'plpgsql';
111 create trigger tg_wslot_biu before insert or update
112 on WSlot for each row execute procedure tg_wslot_biu();
113 -- ************************************************************
114 -- * AFTER UPDATE on PField
115 -- * - Let PSlots of this field follow
116 -- ************************************************************
117 create function tg_pfield_au() returns opaque as '
119 if new.name != old.name then
120 update PSlot set pfname = new.name where pfname = old.name;
124 ' language 'plpgsql';
125 create trigger tg_pfield_au after update
126 on PField for each row execute procedure tg_pfield_au();
127 -- ************************************************************
128 -- * AFTER DELETE on PField
129 -- * - Remove all slots of this patchfield
130 -- ************************************************************
131 create function tg_pfield_ad() returns opaque as '
133 delete from PSlot where pfname = old.name;
136 ' language 'plpgsql';
137 create trigger tg_pfield_ad after delete
138 on PField for each row execute procedure tg_pfield_ad();
139 -- ************************************************************
140 -- * BEFORE INSERT or UPDATE on PSlot
141 -- * - Ensure that our patchfield does exist
142 -- ************************************************************
143 create function tg_pslot_biu() returns opaque as '
148 select into pfrec * from PField where name = ps.pfname;
150 raise exception ''Patchfield "%" does not exist'', ps.pfname;
154 ' language 'plpgsql';
155 create trigger tg_pslot_biu before insert or update
156 on PSlot for each row execute procedure tg_pslot_biu();
157 -- ************************************************************
158 -- * AFTER UPDATE on System
159 -- * - If system name changes let interfaces follow
160 -- ************************************************************
161 create function tg_system_au() returns opaque as '
163 if new.name != old.name then
164 update IFace set sysname = new.name where sysname = old.name;
168 ' language 'plpgsql';
169 create trigger tg_system_au after update
170 on System for each row execute procedure tg_system_au();
171 -- ************************************************************
172 -- * BEFORE INSERT or UPDATE on IFace
173 -- * - set the slotname to IF.sysname.ifname
174 -- ************************************************************
175 create function tg_iface_biu() returns opaque as '
180 select into sysrec * from system where name = new.sysname;
182 raise exception ''system "%" does not exist'', new.sysname;
184 sname := ''IF.'' || new.sysname;
185 sname := sname || ''.'';
186 sname := sname || new.ifname;
187 if length(sname) > 20 then
188 raise exception ''IFace slotname "%" too long (20 char max)'', sname;
190 new.slotname := sname;
193 ' language 'plpgsql';
194 create trigger tg_iface_biu before insert or update
195 on IFace for each row execute procedure tg_iface_biu();
196 -- ************************************************************
197 -- * AFTER INSERT or UPDATE or DELETE on Hub
198 -- * - insert/delete/rename slots as required
199 -- ************************************************************
200 create function tg_hub_a() returns opaque as '
205 if tg_op = ''INSERT'' then
206 dummy := tg_hub_adjustslots(new.name, 0, new.nslots);
209 if tg_op = ''UPDATE'' then
210 if new.name != old.name then
211 update HSlot set hubname = new.name where hubname = old.name;
213 dummy := tg_hub_adjustslots(new.name, old.nslots, new.nslots);
216 if tg_op = ''DELETE'' then
217 dummy := tg_hub_adjustslots(old.name, old.nslots, 0);
221 ' language 'plpgsql';
222 create trigger tg_hub_a after insert or update or delete
223 on Hub for each row execute procedure tg_hub_a();
224 -- ************************************************************
225 -- * Support function to add/remove slots of Hub
226 -- ************************************************************
227 create function tg_hub_adjustslots(bpchar, integer, integer)
231 oldnslots alias for $2;
232 newnslots alias for $3;
234 if newnslots = oldnslots then
237 if newnslots < oldnslots then
238 delete from HSlot where hubname = hname and slotno > newnslots;
241 for i in oldnslots + 1 .. newnslots loop
242 insert into HSlot (slotname, hubname, slotno, slotlink)
243 values (''HS.dummy'', hname, i, '''');
247 ' language 'plpgsql';
248 -- ************************************************************
249 -- * BEFORE INSERT or UPDATE on HSlot
250 -- * - prevent from manual manipulation
251 -- * - set the slotname to HS.hubname.slotno
252 -- ************************************************************
253 create function tg_hslot_biu() returns opaque as '
256 xname HSlot.slotname%TYPE;
259 select into hubrec * from Hub where name = new.hubname;
261 raise exception ''no manual manipulation of HSlot'';
263 if new.slotno < 1 or new.slotno > hubrec.nslots then
264 raise exception ''no manual manipulation of HSlot'';
266 if tg_op = ''UPDATE'' then
267 if new.hubname != old.hubname then
268 if count(*) > 0 from Hub where name = old.hubname then
269 raise exception ''no manual manipulation of HSlot'';
273 sname := ''HS.'' || trim(new.hubname);
274 sname := sname || ''.'';
275 sname := sname || new.slotno::text;
276 if length(sname) > 20 then
277 raise exception ''HSlot slotname "%" too long (20 char max)'', sname;
279 new.slotname := sname;
282 ' language 'plpgsql';
283 create trigger tg_hslot_biu before insert or update
284 on HSlot for each row execute procedure tg_hslot_biu();
285 -- ************************************************************
286 -- * BEFORE DELETE on HSlot
287 -- * - prevent from manual manipulation
288 -- ************************************************************
289 create function tg_hslot_bd() returns opaque as '
293 select into hubrec * from Hub where name = old.hubname;
297 if old.slotno > hubrec.nslots then
300 raise exception ''no manual manipulation of HSlot'';
302 ' language 'plpgsql';
303 create trigger tg_hslot_bd before delete
304 on HSlot for each row execute procedure tg_hslot_bd();
305 -- ************************************************************
306 -- * BEFORE INSERT on all slots
307 -- * - Check name prefix
308 -- ************************************************************
309 create function tg_chkslotname() returns opaque as '
311 if substr(new.slotname, 1, 2) != tg_argv[0] then
312 raise exception ''slotname must begin with %'', tg_argv[0];
316 ' language 'plpgsql';
317 create trigger tg_chkslotname before insert
318 on PSlot for each row execute procedure tg_chkslotname('PS');
319 create trigger tg_chkslotname before insert
320 on WSlot for each row execute procedure tg_chkslotname('WS');
321 create trigger tg_chkslotname before insert
322 on PLine for each row execute procedure tg_chkslotname('PL');
323 create trigger tg_chkslotname before insert
324 on IFace for each row execute procedure tg_chkslotname('IF');
325 create trigger tg_chkslotname before insert
326 on PHone for each row execute procedure tg_chkslotname('PH');
327 -- ************************************************************
328 -- * BEFORE INSERT or UPDATE on all slots with slotlink
329 -- * - Set slotlink to empty string if NULL value given
330 -- ************************************************************
331 create function tg_chkslotlink() returns opaque as '
333 if new.slotlink isnull then
334 new.slotlink := '''';
338 ' language 'plpgsql';
339 create trigger tg_chkslotlink before insert or update
340 on PSlot for each row execute procedure tg_chkslotlink();
341 create trigger tg_chkslotlink before insert or update
342 on WSlot for each row execute procedure tg_chkslotlink();
343 create trigger tg_chkslotlink before insert or update
344 on IFace for each row execute procedure tg_chkslotlink();
345 create trigger tg_chkslotlink before insert or update
346 on HSlot for each row execute procedure tg_chkslotlink();
347 create trigger tg_chkslotlink before insert or update
348 on PHone for each row execute procedure tg_chkslotlink();
349 -- ************************************************************
350 -- * BEFORE INSERT or UPDATE on all slots with backlink
351 -- * - Set backlink to empty string if NULL value given
352 -- ************************************************************
353 create function tg_chkbacklink() returns opaque as '
355 if new.backlink isnull then
356 new.backlink := '''';
360 ' language 'plpgsql';
361 create trigger tg_chkbacklink before insert or update
362 on PSlot for each row execute procedure tg_chkbacklink();
363 create trigger tg_chkbacklink before insert or update
364 on WSlot for each row execute procedure tg_chkbacklink();
365 create trigger tg_chkbacklink before insert or update
366 on PLine for each row execute procedure tg_chkbacklink();
367 -- ************************************************************
368 -- * BEFORE UPDATE on PSlot
369 -- * - do delete/insert instead of update if name changes
370 -- ************************************************************
371 create function tg_pslot_bu() returns opaque as '
373 if new.slotname != old.slotname then
374 delete from PSlot where slotname = old.slotname;
390 ' language 'plpgsql';
391 create trigger tg_pslot_bu before update
392 on PSlot for each row execute procedure tg_pslot_bu();
393 -- ************************************************************
394 -- * BEFORE UPDATE on WSlot
395 -- * - do delete/insert instead of update if name changes
396 -- ************************************************************
397 create function tg_wslot_bu() returns opaque as '
399 if new.slotname != old.slotname then
400 delete from WSlot where slotname = old.slotname;
416 ' language 'plpgsql';
417 create trigger tg_wslot_bu before update
418 on WSlot for each row execute procedure tg_Wslot_bu();
419 -- ************************************************************
420 -- * BEFORE UPDATE on PLine
421 -- * - do delete/insert instead of update if name changes
422 -- ************************************************************
423 create function tg_pline_bu() returns opaque as '
425 if new.slotname != old.slotname then
426 delete from PLine where slotname = old.slotname;
442 ' language 'plpgsql';
443 create trigger tg_pline_bu before update
444 on PLine for each row execute procedure tg_pline_bu();
445 -- ************************************************************
446 -- * BEFORE UPDATE on IFace
447 -- * - do delete/insert instead of update if name changes
448 -- ************************************************************
449 create function tg_iface_bu() returns opaque as '
451 if new.slotname != old.slotname then
452 delete from IFace where slotname = old.slotname;
468 ' language 'plpgsql';
469 create trigger tg_iface_bu before update
470 on IFace for each row execute procedure tg_iface_bu();
471 -- ************************************************************
472 -- * BEFORE UPDATE on HSlot
473 -- * - do delete/insert instead of update if name changes
474 -- ************************************************************
475 create function tg_hslot_bu() returns opaque as '
477 if new.slotname != old.slotname or new.hubname != old.hubname then
478 delete from HSlot where slotname = old.slotname;
494 ' language 'plpgsql';
495 create trigger tg_hslot_bu before update
496 on HSlot for each row execute procedure tg_hslot_bu();
497 -- ************************************************************
498 -- * BEFORE UPDATE on PHone
499 -- * - do delete/insert instead of update if name changes
500 -- ************************************************************
501 create function tg_phone_bu() returns opaque as '
503 if new.slotname != old.slotname then
504 delete from PHone where slotname = old.slotname;
518 ' language 'plpgsql';
519 create trigger tg_phone_bu before update
520 on PHone for each row execute procedure tg_phone_bu();
521 -- ************************************************************
522 -- * AFTER INSERT or UPDATE or DELETE on slot with backlink
523 -- * - Ensure that the opponent correctly points back to us
524 -- ************************************************************
525 create function tg_backlink_a() returns opaque as '
529 if tg_op = ''INSERT'' then
530 if new.backlink != '''' then
531 dummy := tg_backlink_set(new.backlink, new.slotname);
535 if tg_op = ''UPDATE'' then
536 if new.backlink != old.backlink then
537 if old.backlink != '''' then
538 dummy := tg_backlink_unset(old.backlink, old.slotname);
540 if new.backlink != '''' then
541 dummy := tg_backlink_set(new.backlink, new.slotname);
544 if new.slotname != old.slotname and new.backlink != '''' then
545 dummy := tg_slotlink_set(new.backlink, new.slotname);
550 if tg_op = ''DELETE'' then
551 if old.backlink != '''' then
552 dummy := tg_backlink_unset(old.backlink, old.slotname);
557 ' language 'plpgsql';
558 create trigger tg_backlink_a after insert or update or delete
559 on PSlot for each row execute procedure tg_backlink_a('PS');
560 create trigger tg_backlink_a after insert or update or delete
561 on WSlot for each row execute procedure tg_backlink_a('WS');
562 create trigger tg_backlink_a after insert or update or delete
563 on PLine for each row execute procedure tg_backlink_a('PL');
564 -- ************************************************************
565 -- * Support function to set the opponents backlink field
566 -- * if it does not already point to the requested slot
567 -- ************************************************************
568 create function tg_backlink_set(bpchar, bpchar)
577 mytype := substr(myname, 1, 2);
578 link := mytype || substr(blname, 1, 2);
579 if link = ''PLPL'' then
581 ''backlink between two phone lines does not make sense'';
583 if link in (''PLWS'', ''WSPL'') then
585 ''direct link of phone line to wall slot not permitted'';
587 if mytype = ''PS'' then
588 select into rec * from PSlot where slotname = myname;
590 raise exception ''% does not exist'', myname;
592 if rec.backlink != blname then
593 update PSlot set backlink = blname where slotname = myname;
597 if mytype = ''WS'' then
598 select into rec * from WSlot where slotname = myname;
600 raise exception ''% does not exist'', myname;
602 if rec.backlink != blname then
603 update WSlot set backlink = blname where slotname = myname;
607 if mytype = ''PL'' then
608 select into rec * from PLine where slotname = myname;
610 raise exception ''% does not exist'', myname;
612 if rec.backlink != blname then
613 update PLine set backlink = blname where slotname = myname;
617 raise exception ''illegal backlink beginning with %'', mytype;
619 ' language 'plpgsql';
620 -- ************************************************************
621 -- * Support function to clear out the backlink field if
622 -- * it still points to specific slot
623 -- ************************************************************
624 create function tg_backlink_unset(bpchar, bpchar)
632 mytype := substr(myname, 1, 2);
633 if mytype = ''PS'' then
634 select into rec * from PSlot where slotname = myname;
638 if rec.backlink = blname then
639 update PSlot set backlink = '''' where slotname = myname;
643 if mytype = ''WS'' then
644 select into rec * from WSlot where slotname = myname;
648 if rec.backlink = blname then
649 update WSlot set backlink = '''' where slotname = myname;
653 if mytype = ''PL'' then
654 select into rec * from PLine where slotname = myname;
658 if rec.backlink = blname then
659 update PLine set backlink = '''' where slotname = myname;
664 ' language 'plpgsql';
665 -- ************************************************************
666 -- * AFTER INSERT or UPDATE or DELETE on slot with slotlink
667 -- * - Ensure that the opponent correctly points back to us
668 -- ************************************************************
669 create function tg_slotlink_a() returns opaque as '
673 if tg_op = ''INSERT'' then
674 if new.slotlink != '''' then
675 dummy := tg_slotlink_set(new.slotlink, new.slotname);
679 if tg_op = ''UPDATE'' then
680 if new.slotlink != old.slotlink then
681 if old.slotlink != '''' then
682 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
684 if new.slotlink != '''' then
685 dummy := tg_slotlink_set(new.slotlink, new.slotname);
688 if new.slotname != old.slotname and new.slotlink != '''' then
689 dummy := tg_slotlink_set(new.slotlink, new.slotname);
694 if tg_op = ''DELETE'' then
695 if old.slotlink != '''' then
696 dummy := tg_slotlink_unset(old.slotlink, old.slotname);
701 ' language 'plpgsql';
702 create trigger tg_slotlink_a after insert or update or delete
703 on PSlot for each row execute procedure tg_slotlink_a('PS');
704 create trigger tg_slotlink_a after insert or update or delete
705 on WSlot for each row execute procedure tg_slotlink_a('WS');
706 create trigger tg_slotlink_a after insert or update or delete
707 on IFace for each row execute procedure tg_slotlink_a('IF');
708 create trigger tg_slotlink_a after insert or update or delete
709 on HSlot for each row execute procedure tg_slotlink_a('HS');
710 create trigger tg_slotlink_a after insert or update or delete
711 on PHone for each row execute procedure tg_slotlink_a('PH');
712 -- ************************************************************
713 -- * Support function to set the opponents slotlink field
714 -- * if it does not already point to the requested slot
715 -- ************************************************************
716 create function tg_slotlink_set(bpchar, bpchar)
725 mytype := substr(myname, 1, 2);
726 link := mytype || substr(blname, 1, 2);
727 if link = ''PHPH'' then
729 ''slotlink between two phones does not make sense'';
731 if link in (''PHHS'', ''HSPH'') then
733 ''link of phone to hub does not make sense'';
735 if link in (''PHIF'', ''IFPH'') then
737 ''link of phone to hub does not make sense'';
739 if link in (''PSWS'', ''WSPS'') then
741 ''slotlink from patchslot to wallslot not permitted'';
743 if mytype = ''PS'' then
744 select into rec * from PSlot where slotname = myname;
746 raise exception ''% does not exist'', myname;
748 if rec.slotlink != blname then
749 update PSlot set slotlink = blname where slotname = myname;
753 if mytype = ''WS'' then
754 select into rec * from WSlot where slotname = myname;
756 raise exception ''% does not exist'', myname;
758 if rec.slotlink != blname then
759 update WSlot set slotlink = blname where slotname = myname;
763 if mytype = ''IF'' then
764 select into rec * from IFace where slotname = myname;
766 raise exception ''% does not exist'', myname;
768 if rec.slotlink != blname then
769 update IFace set slotlink = blname where slotname = myname;
773 if mytype = ''HS'' then
774 select into rec * from HSlot where slotname = myname;
776 raise exception ''% does not exist'', myname;
778 if rec.slotlink != blname then
779 update HSlot set slotlink = blname where slotname = myname;
783 if mytype = ''PH'' then
784 select into rec * from PHone where slotname = myname;
786 raise exception ''% does not exist'', myname;
788 if rec.slotlink != blname then
789 update PHone set slotlink = blname where slotname = myname;
793 raise exception ''illegal slotlink beginning with %'', mytype;
795 ' language 'plpgsql';
796 -- ************************************************************
797 -- * Support function to clear out the slotlink field if
798 -- * it still points to specific slot
799 -- ************************************************************
800 create function tg_slotlink_unset(bpchar, bpchar)
808 mytype := substr(myname, 1, 2);
809 if mytype = ''PS'' then
810 select into rec * from PSlot where slotname = myname;
814 if rec.slotlink = blname then
815 update PSlot set slotlink = '''' where slotname = myname;
819 if mytype = ''WS'' then
820 select into rec * from WSlot where slotname = myname;
824 if rec.slotlink = blname then
825 update WSlot set slotlink = '''' where slotname = myname;
829 if mytype = ''IF'' then
830 select into rec * from IFace where slotname = myname;
834 if rec.slotlink = blname then
835 update IFace set slotlink = '''' where slotname = myname;
839 if mytype = ''HS'' then
840 select into rec * from HSlot where slotname = myname;
844 if rec.slotlink = blname then
845 update HSlot set slotlink = '''' where slotname = myname;
849 if mytype = ''PH'' then
850 select into rec * from PHone where slotname = myname;
854 if rec.slotlink = blname then
855 update PHone set slotlink = '''' where slotname = myname;
860 ' language 'plpgsql';
861 -- ************************************************************
862 -- * Describe the backside of a patchfield slot
863 -- ************************************************************
864 create function pslot_backlink_view(bpchar)
872 select into rec * from PSlot where slotname = $1;
876 if rec.backlink = '''' then
879 bltype := substr(rec.backlink, 1, 2);
880 if bltype = ''PL'' then
884 select into rec * from PLine where slotname = outer.rec.backlink;
885 retval := ''Phone line '' || trim(rec.phonenumber);
886 if rec.comment != '''' then
887 retval := retval || '' ('';
888 retval := retval || rec.comment;
889 retval := retval || '')'';
894 if bltype = ''WS'' then
895 select into rec * from WSlot where slotname = rec.backlink;
896 retval := trim(rec.slotname) || '' in room '';
897 retval := retval || trim(rec.roomno);
898 retval := retval || '' -> '';
899 return retval || wslot_slotlink_view(rec.slotname);
903 ' language 'plpgsql';
904 -- ************************************************************
905 -- * Describe the front of a patchfield slot
906 -- ************************************************************
907 create function pslot_slotlink_view(bpchar)
914 select into psrec * from PSlot where slotname = $1;
918 if psrec.slotlink = '''' then
921 sltype := substr(psrec.slotlink, 1, 2);
922 if sltype = ''PS'' then
923 retval := trim(psrec.slotlink) || '' -> '';
924 return retval || pslot_backlink_view(psrec.slotlink);
926 if sltype = ''HS'' then
927 retval := comment from Hub H, HSlot HS
928 where HS.slotname = psrec.slotlink
929 and H.name = HS.hubname;
930 retval := retval || '' slot '';
931 retval := retval || slotno::text from HSlot
932 where slotname = psrec.slotlink;
935 return psrec.slotlink;
937 ' language 'plpgsql';
938 -- ************************************************************
939 -- * Describe the front of a wall connector slot
940 -- ************************************************************
941 create function wslot_slotlink_view(bpchar)
948 select into rec * from WSlot where slotname = $1;
952 if rec.slotlink = '''' then
955 sltype := substr(rec.slotlink, 1, 2);
956 if sltype = ''PH'' then
957 select into rec * from PHone where slotname = rec.slotlink;
958 retval := ''Phone '' || trim(rec.slotname);
959 if rec.comment != '''' then
960 retval := retval || '' ('';
961 retval := retval || rec.comment;
962 retval := retval || '')'';
966 if sltype = ''IF'' then
968 syrow System%RowType;
971 select into ifrow * from IFace where slotname = rec.slotlink;
972 select into syrow * from System where name = ifrow.sysname;
973 retval := syrow.name || '' IF '';
974 retval := retval || ifrow.ifname;
975 if syrow.comment != '''' then
976 retval := retval || '' ('';
977 retval := retval || syrow.comment;
978 retval := retval || '')'';
985 ' language 'plpgsql';
986 -- ************************************************************
987 -- * View of a patchfield describing backside and patches
988 -- ************************************************************
989 create view Pfield_v1 as select PF.pfname, PF.slotname,
990 pslot_backlink_view(PF.slotname) as backside,
991 pslot_slotlink_view(PF.slotname) as patch
994 -- First we build the house - so we create the rooms
996 insert into Room values ('001', 'Entrance');
997 insert into Room values ('002', 'Office');
998 insert into Room values ('003', 'Office');
999 insert into Room values ('004', 'Technical');
1000 insert into Room values ('101', 'Office');
1001 insert into Room values ('102', 'Conference');
1002 insert into Room values ('103', 'Restroom');
1003 insert into Room values ('104', 'Technical');
1004 insert into Room values ('105', 'Office');
1005 insert into Room values ('106', 'Office');
1007 -- Second we install the wall connectors
1009 insert into WSlot values ('WS.001.1a', '001', '', '');
1010 insert into WSlot values ('WS.001.1b', '001', '', '');
1011 insert into WSlot values ('WS.001.2a', '001', '', '');
1012 insert into WSlot values ('WS.001.2b', '001', '', '');
1013 insert into WSlot values ('WS.001.3a', '001', '', '');
1014 insert into WSlot values ('WS.001.3b', '001', '', '');
1015 insert into WSlot values ('WS.002.1a', '002', '', '');
1016 insert into WSlot values ('WS.002.1b', '002', '', '');
1017 insert into WSlot values ('WS.002.2a', '002', '', '');
1018 insert into WSlot values ('WS.002.2b', '002', '', '');
1019 insert into WSlot values ('WS.002.3a', '002', '', '');
1020 insert into WSlot values ('WS.002.3b', '002', '', '');
1021 insert into WSlot values ('WS.003.1a', '003', '', '');
1022 insert into WSlot values ('WS.003.1b', '003', '', '');
1023 insert into WSlot values ('WS.003.2a', '003', '', '');
1024 insert into WSlot values ('WS.003.2b', '003', '', '');
1025 insert into WSlot values ('WS.003.3a', '003', '', '');
1026 insert into WSlot values ('WS.003.3b', '003', '', '');
1027 insert into WSlot values ('WS.101.1a', '101', '', '');
1028 insert into WSlot values ('WS.101.1b', '101', '', '');
1029 insert into WSlot values ('WS.101.2a', '101', '', '');
1030 insert into WSlot values ('WS.101.2b', '101', '', '');
1031 insert into WSlot values ('WS.101.3a', '101', '', '');
1032 insert into WSlot values ('WS.101.3b', '101', '', '');
1033 insert into WSlot values ('WS.102.1a', '102', '', '');
1034 insert into WSlot values ('WS.102.1b', '102', '', '');
1035 insert into WSlot values ('WS.102.2a', '102', '', '');
1036 insert into WSlot values ('WS.102.2b', '102', '', '');
1037 insert into WSlot values ('WS.102.3a', '102', '', '');
1038 insert into WSlot values ('WS.102.3b', '102', '', '');
1039 insert into WSlot values ('WS.105.1a', '105', '', '');
1040 insert into WSlot values ('WS.105.1b', '105', '', '');
1041 insert into WSlot values ('WS.105.2a', '105', '', '');
1042 insert into WSlot values ('WS.105.2b', '105', '', '');
1043 insert into WSlot values ('WS.105.3a', '105', '', '');
1044 insert into WSlot values ('WS.105.3b', '105', '', '');
1045 insert into WSlot values ('WS.106.1a', '106', '', '');
1046 insert into WSlot values ('WS.106.1b', '106', '', '');
1047 insert into WSlot values ('WS.106.2a', '106', '', '');
1048 insert into WSlot values ('WS.106.2b', '106', '', '');
1049 insert into WSlot values ('WS.106.3a', '106', '', '');
1050 insert into WSlot values ('WS.106.3b', '106', '', '');
1052 -- Now create the patch fields and their slots
1054 insert into PField values ('PF0_1', 'Wallslots basement');
1056 -- The cables for these will be made later, so they are unconnected for now
1058 insert into PSlot values ('PS.base.a1', 'PF0_1', '', '');
1059 insert into PSlot values ('PS.base.a2', 'PF0_1', '', '');
1060 insert into PSlot values ('PS.base.a3', 'PF0_1', '', '');
1061 insert into PSlot values ('PS.base.a4', 'PF0_1', '', '');
1062 insert into PSlot values ('PS.base.a5', 'PF0_1', '', '');
1063 insert into PSlot values ('PS.base.a6', 'PF0_1', '', '');
1065 -- These are already wired to the wall connectors
1067 insert into PSlot values ('PS.base.b1', 'PF0_1', '', 'WS.002.1a');
1068 insert into PSlot values ('PS.base.b2', 'PF0_1', '', 'WS.002.1b');
1069 insert into PSlot values ('PS.base.b3', 'PF0_1', '', 'WS.002.2a');
1070 insert into PSlot values ('PS.base.b4', 'PF0_1', '', 'WS.002.2b');
1071 insert into PSlot values ('PS.base.b5', 'PF0_1', '', 'WS.002.3a');
1072 insert into PSlot values ('PS.base.b6', 'PF0_1', '', 'WS.002.3b');
1073 insert into PSlot values ('PS.base.c1', 'PF0_1', '', 'WS.003.1a');
1074 insert into PSlot values ('PS.base.c2', 'PF0_1', '', 'WS.003.1b');
1075 insert into PSlot values ('PS.base.c3', 'PF0_1', '', 'WS.003.2a');
1076 insert into PSlot values ('PS.base.c4', 'PF0_1', '', 'WS.003.2b');
1077 insert into PSlot values ('PS.base.c5', 'PF0_1', '', 'WS.003.3a');
1078 insert into PSlot values ('PS.base.c6', 'PF0_1', '', 'WS.003.3b');
1080 -- This patchfield will be renamed later into PF0_2 - so its
1081 -- slots references in pfname should follow
1083 insert into PField values ('PF0_X', 'Phonelines basement');
1084 insert into PSlot values ('PS.base.ta1', 'PF0_X', '', '');
1085 insert into PSlot values ('PS.base.ta2', 'PF0_X', '', '');
1086 insert into PSlot values ('PS.base.ta3', 'PF0_X', '', '');
1087 insert into PSlot values ('PS.base.ta4', 'PF0_X', '', '');
1088 insert into PSlot values ('PS.base.ta5', 'PF0_X', '', '');
1089 insert into PSlot values ('PS.base.ta6', 'PF0_X', '', '');
1090 insert into PSlot values ('PS.base.tb1', 'PF0_X', '', '');
1091 insert into PSlot values ('PS.base.tb2', 'PF0_X', '', '');
1092 insert into PSlot values ('PS.base.tb3', 'PF0_X', '', '');
1093 insert into PSlot values ('PS.base.tb4', 'PF0_X', '', '');
1094 insert into PSlot values ('PS.base.tb5', 'PF0_X', '', '');
1095 insert into PSlot values ('PS.base.tb6', 'PF0_X', '', '');
1096 insert into PField values ('PF1_1', 'Wallslots 1st floor');
1097 insert into PSlot values ('PS.1st.a1', 'PF1_1', '', 'WS.101.1a');
1098 insert into PSlot values ('PS.1st.a2', 'PF1_1', '', 'WS.101.1b');
1099 insert into PSlot values ('PS.1st.a3', 'PF1_1', '', 'WS.101.2a');
1100 insert into PSlot values ('PS.1st.a4', 'PF1_1', '', 'WS.101.2b');
1101 insert into PSlot values ('PS.1st.a5', 'PF1_1', '', 'WS.101.3a');
1102 insert into PSlot values ('PS.1st.a6', 'PF1_1', '', 'WS.101.3b');
1103 insert into PSlot values ('PS.1st.b1', 'PF1_1', '', 'WS.102.1a');
1104 insert into PSlot values ('PS.1st.b2', 'PF1_1', '', 'WS.102.1b');
1105 insert into PSlot values ('PS.1st.b3', 'PF1_1', '', 'WS.102.2a');
1106 insert into PSlot values ('PS.1st.b4', 'PF1_1', '', 'WS.102.2b');
1107 insert into PSlot values ('PS.1st.b5', 'PF1_1', '', 'WS.102.3a');
1108 insert into PSlot values ('PS.1st.b6', 'PF1_1', '', 'WS.102.3b');
1109 insert into PSlot values ('PS.1st.c1', 'PF1_1', '', 'WS.105.1a');
1110 insert into PSlot values ('PS.1st.c2', 'PF1_1', '', 'WS.105.1b');
1111 insert into PSlot values ('PS.1st.c3', 'PF1_1', '', 'WS.105.2a');
1112 insert into PSlot values ('PS.1st.c4', 'PF1_1', '', 'WS.105.2b');
1113 insert into PSlot values ('PS.1st.c5', 'PF1_1', '', 'WS.105.3a');
1114 insert into PSlot values ('PS.1st.c6', 'PF1_1', '', 'WS.105.3b');
1115 insert into PSlot values ('PS.1st.d1', 'PF1_1', '', 'WS.106.1a');
1116 insert into PSlot values ('PS.1st.d2', 'PF1_1', '', 'WS.106.1b');
1117 insert into PSlot values ('PS.1st.d3', 'PF1_1', '', 'WS.106.2a');
1118 insert into PSlot values ('PS.1st.d4', 'PF1_1', '', 'WS.106.2b');
1119 insert into PSlot values ('PS.1st.d5', 'PF1_1', '', 'WS.106.3a');
1120 insert into PSlot values ('PS.1st.d6', 'PF1_1', '', 'WS.106.3b');
1122 -- Now we wire the wall connectors 1a-2a in room 001 to the
1123 -- patchfield. In the second update we make an error, and
1126 update PSlot set backlink = 'WS.001.1a' where slotname = 'PS.base.a1';
1127 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a3';
1128 select * from WSlot where roomno = '001' order by slotname;
1129 slotname | roomno | slotlink | backlink
1130 ----------------------+----------+----------------------+----------------------
1131 WS.001.1a | 001 | | PS.base.a1
1132 WS.001.1b | 001 | | PS.base.a3
1139 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1140 slotname | pfname | slotlink | backlink
1141 ----------------------+--------+----------------------+----------------------
1142 PS.base.a1 | PF0_1 | | WS.001.1a
1143 PS.base.a2 | PF0_1 | |
1144 PS.base.a3 | PF0_1 | | WS.001.1b
1145 PS.base.a4 | PF0_1 | |
1146 PS.base.a5 | PF0_1 | |
1147 PS.base.a6 | PF0_1 | |
1150 update PSlot set backlink = 'WS.001.2a' where slotname = 'PS.base.a3';
1151 select * from WSlot where roomno = '001' order by slotname;
1152 slotname | roomno | slotlink | backlink
1153 ----------------------+----------+----------------------+----------------------
1154 WS.001.1a | 001 | | PS.base.a1
1156 WS.001.2a | 001 | | PS.base.a3
1162 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1163 slotname | pfname | slotlink | backlink
1164 ----------------------+--------+----------------------+----------------------
1165 PS.base.a1 | PF0_1 | | WS.001.1a
1166 PS.base.a2 | PF0_1 | |
1167 PS.base.a3 | PF0_1 | | WS.001.2a
1168 PS.base.a4 | PF0_1 | |
1169 PS.base.a5 | PF0_1 | |
1170 PS.base.a6 | PF0_1 | |
1173 update PSlot set backlink = 'WS.001.1b' where slotname = 'PS.base.a2';
1174 select * from WSlot where roomno = '001' order by slotname;
1175 slotname | roomno | slotlink | backlink
1176 ----------------------+----------+----------------------+----------------------
1177 WS.001.1a | 001 | | PS.base.a1
1178 WS.001.1b | 001 | | PS.base.a2
1179 WS.001.2a | 001 | | PS.base.a3
1185 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1186 slotname | pfname | slotlink | backlink
1187 ----------------------+--------+----------------------+----------------------
1188 PS.base.a1 | PF0_1 | | WS.001.1a
1189 PS.base.a2 | PF0_1 | | WS.001.1b
1190 PS.base.a3 | PF0_1 | | WS.001.2a
1191 PS.base.a4 | PF0_1 | |
1192 PS.base.a5 | PF0_1 | |
1193 PS.base.a6 | PF0_1 | |
1197 -- Same procedure for 2b-3b but this time updating the WSlot instead
1198 -- of the PSlot. Due to the triggers the result is the same:
1199 -- WSlot and corresponding PSlot point to each other.
1201 update WSlot set backlink = 'PS.base.a4' where slotname = 'WS.001.2b';
1202 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3a';
1203 select * from WSlot where roomno = '001' order by slotname;
1204 slotname | roomno | slotlink | backlink
1205 ----------------------+----------+----------------------+----------------------
1206 WS.001.1a | 001 | | PS.base.a1
1207 WS.001.1b | 001 | | PS.base.a2
1208 WS.001.2a | 001 | | PS.base.a3
1209 WS.001.2b | 001 | | PS.base.a4
1210 WS.001.3a | 001 | | PS.base.a6
1214 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1215 slotname | pfname | slotlink | backlink
1216 ----------------------+--------+----------------------+----------------------
1217 PS.base.a1 | PF0_1 | | WS.001.1a
1218 PS.base.a2 | PF0_1 | | WS.001.1b
1219 PS.base.a3 | PF0_1 | | WS.001.2a
1220 PS.base.a4 | PF0_1 | | WS.001.2b
1221 PS.base.a5 | PF0_1 | |
1222 PS.base.a6 | PF0_1 | | WS.001.3a
1225 update WSlot set backlink = 'PS.base.a6' where slotname = 'WS.001.3b';
1226 select * from WSlot where roomno = '001' order by slotname;
1227 slotname | roomno | slotlink | backlink
1228 ----------------------+----------+----------------------+----------------------
1229 WS.001.1a | 001 | | PS.base.a1
1230 WS.001.1b | 001 | | PS.base.a2
1231 WS.001.2a | 001 | | PS.base.a3
1232 WS.001.2b | 001 | | PS.base.a4
1234 WS.001.3b | 001 | | PS.base.a6
1237 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1238 slotname | pfname | slotlink | backlink
1239 ----------------------+--------+----------------------+----------------------
1240 PS.base.a1 | PF0_1 | | WS.001.1a
1241 PS.base.a2 | PF0_1 | | WS.001.1b
1242 PS.base.a3 | PF0_1 | | WS.001.2a
1243 PS.base.a4 | PF0_1 | | WS.001.2b
1244 PS.base.a5 | PF0_1 | |
1245 PS.base.a6 | PF0_1 | | WS.001.3b
1248 update WSlot set backlink = 'PS.base.a5' where slotname = 'WS.001.3a';
1249 select * from WSlot where roomno = '001' order by slotname;
1250 slotname | roomno | slotlink | backlink
1251 ----------------------+----------+----------------------+----------------------
1252 WS.001.1a | 001 | | PS.base.a1
1253 WS.001.1b | 001 | | PS.base.a2
1254 WS.001.2a | 001 | | PS.base.a3
1255 WS.001.2b | 001 | | PS.base.a4
1256 WS.001.3a | 001 | | PS.base.a5
1257 WS.001.3b | 001 | | PS.base.a6
1260 select * from PSlot where slotname ~ 'PS.base.a' order by slotname;
1261 slotname | pfname | slotlink | backlink
1262 ----------------------+--------+----------------------+----------------------
1263 PS.base.a1 | PF0_1 | | WS.001.1a
1264 PS.base.a2 | PF0_1 | | WS.001.1b
1265 PS.base.a3 | PF0_1 | | WS.001.2a
1266 PS.base.a4 | PF0_1 | | WS.001.2b
1267 PS.base.a5 | PF0_1 | | WS.001.3a
1268 PS.base.a6 | PF0_1 | | WS.001.3b
1271 insert into PField values ('PF1_2', 'Phonelines 1st floor');
1272 insert into PSlot values ('PS.1st.ta1', 'PF1_2', '', '');
1273 insert into PSlot values ('PS.1st.ta2', 'PF1_2', '', '');
1274 insert into PSlot values ('PS.1st.ta3', 'PF1_2', '', '');
1275 insert into PSlot values ('PS.1st.ta4', 'PF1_2', '', '');
1276 insert into PSlot values ('PS.1st.ta5', 'PF1_2', '', '');
1277 insert into PSlot values ('PS.1st.ta6', 'PF1_2', '', '');
1278 insert into PSlot values ('PS.1st.tb1', 'PF1_2', '', '');
1279 insert into PSlot values ('PS.1st.tb2', 'PF1_2', '', '');
1280 insert into PSlot values ('PS.1st.tb3', 'PF1_2', '', '');
1281 insert into PSlot values ('PS.1st.tb4', 'PF1_2', '', '');
1282 insert into PSlot values ('PS.1st.tb5', 'PF1_2', '', '');
1283 insert into PSlot values ('PS.1st.tb6', 'PF1_2', '', '');
1285 -- Fix the wrong name for patchfield PF0_2
1287 update PField set name = 'PF0_2' where name = 'PF0_X';
1288 select * from PSlot order by slotname;
1289 slotname | pfname | slotlink | backlink
1290 ----------------------+--------+----------------------+----------------------
1291 PS.1st.a1 | PF1_1 | | WS.101.1a
1292 PS.1st.a2 | PF1_1 | | WS.101.1b
1293 PS.1st.a3 | PF1_1 | | WS.101.2a
1294 PS.1st.a4 | PF1_1 | | WS.101.2b
1295 PS.1st.a5 | PF1_1 | | WS.101.3a
1296 PS.1st.a6 | PF1_1 | | WS.101.3b
1297 PS.1st.b1 | PF1_1 | | WS.102.1a
1298 PS.1st.b2 | PF1_1 | | WS.102.1b
1299 PS.1st.b3 | PF1_1 | | WS.102.2a
1300 PS.1st.b4 | PF1_1 | | WS.102.2b
1301 PS.1st.b5 | PF1_1 | | WS.102.3a
1302 PS.1st.b6 | PF1_1 | | WS.102.3b
1303 PS.1st.c1 | PF1_1 | | WS.105.1a
1304 PS.1st.c2 | PF1_1 | | WS.105.1b
1305 PS.1st.c3 | PF1_1 | | WS.105.2a
1306 PS.1st.c4 | PF1_1 | | WS.105.2b
1307 PS.1st.c5 | PF1_1 | | WS.105.3a
1308 PS.1st.c6 | PF1_1 | | WS.105.3b
1309 PS.1st.d1 | PF1_1 | | WS.106.1a
1310 PS.1st.d2 | PF1_1 | | WS.106.1b
1311 PS.1st.d3 | PF1_1 | | WS.106.2a
1312 PS.1st.d4 | PF1_1 | | WS.106.2b
1313 PS.1st.d5 | PF1_1 | | WS.106.3a
1314 PS.1st.d6 | PF1_1 | | WS.106.3b
1315 PS.1st.ta1 | PF1_2 | |
1316 PS.1st.ta2 | PF1_2 | |
1317 PS.1st.ta3 | PF1_2 | |
1318 PS.1st.ta4 | PF1_2 | |
1319 PS.1st.ta5 | PF1_2 | |
1320 PS.1st.ta6 | PF1_2 | |
1321 PS.1st.tb1 | PF1_2 | |
1322 PS.1st.tb2 | PF1_2 | |
1323 PS.1st.tb3 | PF1_2 | |
1324 PS.1st.tb4 | PF1_2 | |
1325 PS.1st.tb5 | PF1_2 | |
1326 PS.1st.tb6 | PF1_2 | |
1327 PS.base.a1 | PF0_1 | | WS.001.1a
1328 PS.base.a2 | PF0_1 | | WS.001.1b
1329 PS.base.a3 | PF0_1 | | WS.001.2a
1330 PS.base.a4 | PF0_1 | | WS.001.2b
1331 PS.base.a5 | PF0_1 | | WS.001.3a
1332 PS.base.a6 | PF0_1 | | WS.001.3b
1333 PS.base.b1 | PF0_1 | | WS.002.1a
1334 PS.base.b2 | PF0_1 | | WS.002.1b
1335 PS.base.b3 | PF0_1 | | WS.002.2a
1336 PS.base.b4 | PF0_1 | | WS.002.2b
1337 PS.base.b5 | PF0_1 | | WS.002.3a
1338 PS.base.b6 | PF0_1 | | WS.002.3b
1339 PS.base.c1 | PF0_1 | | WS.003.1a
1340 PS.base.c2 | PF0_1 | | WS.003.1b
1341 PS.base.c3 | PF0_1 | | WS.003.2a
1342 PS.base.c4 | PF0_1 | | WS.003.2b
1343 PS.base.c5 | PF0_1 | | WS.003.3a
1344 PS.base.c6 | PF0_1 | | WS.003.3b
1345 PS.base.ta1 | PF0_2 | |
1346 PS.base.ta2 | PF0_2 | |
1347 PS.base.ta3 | PF0_2 | |
1348 PS.base.ta4 | PF0_2 | |
1349 PS.base.ta5 | PF0_2 | |
1350 PS.base.ta6 | PF0_2 | |
1351 PS.base.tb1 | PF0_2 | |
1352 PS.base.tb2 | PF0_2 | |
1353 PS.base.tb3 | PF0_2 | |
1354 PS.base.tb4 | PF0_2 | |
1355 PS.base.tb5 | PF0_2 | |
1356 PS.base.tb6 | PF0_2 | |
1359 select * from WSlot order by slotname;
1360 slotname | roomno | slotlink | backlink
1361 ----------------------+----------+----------------------+----------------------
1362 WS.001.1a | 001 | | PS.base.a1
1363 WS.001.1b | 001 | | PS.base.a2
1364 WS.001.2a | 001 | | PS.base.a3
1365 WS.001.2b | 001 | | PS.base.a4
1366 WS.001.3a | 001 | | PS.base.a5
1367 WS.001.3b | 001 | | PS.base.a6
1368 WS.002.1a | 002 | | PS.base.b1
1369 WS.002.1b | 002 | | PS.base.b2
1370 WS.002.2a | 002 | | PS.base.b3
1371 WS.002.2b | 002 | | PS.base.b4
1372 WS.002.3a | 002 | | PS.base.b5
1373 WS.002.3b | 002 | | PS.base.b6
1374 WS.003.1a | 003 | | PS.base.c1
1375 WS.003.1b | 003 | | PS.base.c2
1376 WS.003.2a | 003 | | PS.base.c3
1377 WS.003.2b | 003 | | PS.base.c4
1378 WS.003.3a | 003 | | PS.base.c5
1379 WS.003.3b | 003 | | PS.base.c6
1380 WS.101.1a | 101 | | PS.1st.a1
1381 WS.101.1b | 101 | | PS.1st.a2
1382 WS.101.2a | 101 | | PS.1st.a3
1383 WS.101.2b | 101 | | PS.1st.a4
1384 WS.101.3a | 101 | | PS.1st.a5
1385 WS.101.3b | 101 | | PS.1st.a6
1386 WS.102.1a | 102 | | PS.1st.b1
1387 WS.102.1b | 102 | | PS.1st.b2
1388 WS.102.2a | 102 | | PS.1st.b3
1389 WS.102.2b | 102 | | PS.1st.b4
1390 WS.102.3a | 102 | | PS.1st.b5
1391 WS.102.3b | 102 | | PS.1st.b6
1392 WS.105.1a | 105 | | PS.1st.c1
1393 WS.105.1b | 105 | | PS.1st.c2
1394 WS.105.2a | 105 | | PS.1st.c3
1395 WS.105.2b | 105 | | PS.1st.c4
1396 WS.105.3a | 105 | | PS.1st.c5
1397 WS.105.3b | 105 | | PS.1st.c6
1398 WS.106.1a | 106 | | PS.1st.d1
1399 WS.106.1b | 106 | | PS.1st.d2
1400 WS.106.2a | 106 | | PS.1st.d3
1401 WS.106.2b | 106 | | PS.1st.d4
1402 WS.106.3a | 106 | | PS.1st.d5
1403 WS.106.3b | 106 | | PS.1st.d6
1407 -- Install the central phone system and create the phone numbers.
1408 -- They are weired on insert to the patchfields. Again the
1409 -- triggers automatically tell the PSlots to update their
1412 insert into PLine values ('PL.001', '-0', 'Central call', 'PS.base.ta1');
1413 insert into PLine values ('PL.002', '-101', '', 'PS.base.ta2');
1414 insert into PLine values ('PL.003', '-102', '', 'PS.base.ta3');
1415 insert into PLine values ('PL.004', '-103', '', 'PS.base.ta5');
1416 insert into PLine values ('PL.005', '-104', '', 'PS.base.ta6');
1417 insert into PLine values ('PL.006', '-106', '', 'PS.base.tb2');
1418 insert into PLine values ('PL.007', '-108', '', 'PS.base.tb3');
1419 insert into PLine values ('PL.008', '-109', '', 'PS.base.tb4');
1420 insert into PLine values ('PL.009', '-121', '', 'PS.base.tb5');
1421 insert into PLine values ('PL.010', '-122', '', 'PS.base.tb6');
1422 insert into PLine values ('PL.015', '-134', '', 'PS.1st.ta1');
1423 insert into PLine values ('PL.016', '-137', '', 'PS.1st.ta3');
1424 insert into PLine values ('PL.017', '-139', '', 'PS.1st.ta4');
1425 insert into PLine values ('PL.018', '-362', '', 'PS.1st.tb1');
1426 insert into PLine values ('PL.019', '-363', '', 'PS.1st.tb2');
1427 insert into PLine values ('PL.020', '-364', '', 'PS.1st.tb3');
1428 insert into PLine values ('PL.021', '-365', '', 'PS.1st.tb5');
1429 insert into PLine values ('PL.022', '-367', '', 'PS.1st.tb6');
1430 insert into PLine values ('PL.028', '-501', 'Fax entrance', 'PS.base.ta2');
1431 insert into PLine values ('PL.029', '-502', 'Fax 1st floor', 'PS.1st.ta1');
1433 -- Buy some phones, plug them into the wall and patch the
1434 -- phone lines to the corresponding patchfield slots.
1436 insert into PHone values ('PH.hc001', 'Hicom standard', 'WS.001.1a');
1437 update PSlot set slotlink = 'PS.base.ta1' where slotname = 'PS.base.a1';
1438 insert into PHone values ('PH.hc002', 'Hicom standard', 'WS.002.1a');
1439 update PSlot set slotlink = 'PS.base.ta5' where slotname = 'PS.base.b1';
1440 insert into PHone values ('PH.hc003', 'Hicom standard', 'WS.002.2a');
1441 update PSlot set slotlink = 'PS.base.tb2' where slotname = 'PS.base.b3';
1442 insert into PHone values ('PH.fax001', 'Canon fax', 'WS.001.2a');
1443 update PSlot set slotlink = 'PS.base.ta2' where slotname = 'PS.base.a3';
1445 -- Install a hub at one of the patchfields, plug a computers
1446 -- ethernet interface into the wall and patch it to the hub.
1448 insert into Hub values ('base.hub1', 'Patchfield PF0_1 hub', 16);
1449 insert into System values ('orion', 'PC');
1450 insert into IFace values ('IF', 'orion', 'eth0', 'WS.002.1b');
1451 update PSlot set slotlink = 'HS.base.hub1.1' where slotname = 'PS.base.b2';
1453 -- Now we take a look at the patchfield
1455 select * from PField_v1 where pfname = 'PF0_1' order by slotname;
1456 pfname | slotname | backside | patch
1457 --------+----------------------+----------------------------------------------------------+-----------------------------------------------
1458 PF0_1 | PS.base.a1 | WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard) | PS.base.ta1 -> Phone line -0 (Central call)
1459 PF0_1 | PS.base.a2 | WS.001.1b in room 001 -> - | -
1460 PF0_1 | PS.base.a3 | WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax) | PS.base.ta2 -> Phone line -501 (Fax entrance)
1461 PF0_1 | PS.base.a4 | WS.001.2b in room 001 -> - | -
1462 PF0_1 | PS.base.a5 | WS.001.3a in room 001 -> - | -
1463 PF0_1 | PS.base.a6 | WS.001.3b in room 001 -> - | -
1464 PF0_1 | PS.base.b1 | WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard) | PS.base.ta5 -> Phone line -103
1465 PF0_1 | PS.base.b2 | WS.002.1b in room 002 -> orion IF eth0 (PC) | Patchfield PF0_1 hub slot 1
1466 PF0_1 | PS.base.b3 | WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard) | PS.base.tb2 -> Phone line -106
1467 PF0_1 | PS.base.b4 | WS.002.2b in room 002 -> - | -
1468 PF0_1 | PS.base.b5 | WS.002.3a in room 002 -> - | -
1469 PF0_1 | PS.base.b6 | WS.002.3b in room 002 -> - | -
1470 PF0_1 | PS.base.c1 | WS.003.1a in room 003 -> - | -
1471 PF0_1 | PS.base.c2 | WS.003.1b in room 003 -> - | -
1472 PF0_1 | PS.base.c3 | WS.003.2a in room 003 -> - | -
1473 PF0_1 | PS.base.c4 | WS.003.2b in room 003 -> - | -
1474 PF0_1 | PS.base.c5 | WS.003.3a in room 003 -> - | -
1475 PF0_1 | PS.base.c6 | WS.003.3b in room 003 -> - | -
1478 select * from PField_v1 where pfname = 'PF0_2' order by slotname;
1479 pfname | slotname | backside | patch
1480 --------+----------------------+--------------------------------+------------------------------------------------------------------------
1481 PF0_2 | PS.base.ta1 | Phone line -0 (Central call) | PS.base.a1 -> WS.001.1a in room 001 -> Phone PH.hc001 (Hicom standard)
1482 PF0_2 | PS.base.ta2 | Phone line -501 (Fax entrance) | PS.base.a3 -> WS.001.2a in room 001 -> Phone PH.fax001 (Canon fax)
1483 PF0_2 | PS.base.ta3 | Phone line -102 | -
1484 PF0_2 | PS.base.ta4 | - | -
1485 PF0_2 | PS.base.ta5 | Phone line -103 | PS.base.b1 -> WS.002.1a in room 002 -> Phone PH.hc002 (Hicom standard)
1486 PF0_2 | PS.base.ta6 | Phone line -104 | -
1487 PF0_2 | PS.base.tb1 | - | -
1488 PF0_2 | PS.base.tb2 | Phone line -106 | PS.base.b3 -> WS.002.2a in room 002 -> Phone PH.hc003 (Hicom standard)
1489 PF0_2 | PS.base.tb3 | Phone line -108 | -
1490 PF0_2 | PS.base.tb4 | Phone line -109 | -
1491 PF0_2 | PS.base.tb5 | Phone line -121 | -
1492 PF0_2 | PS.base.tb6 | Phone line -122 | -
1496 -- Finally we want errors
1498 insert into PField values ('PF1_1', 'should fail due to unique index');
1499 ERROR: Cannot insert a duplicate key into unique index pfield_name
1500 update PSlot set backlink = 'WS.not.there' where slotname = 'PS.base.a1';
1501 ERROR: WS.not.there does not exist
1502 update PSlot set backlink = 'XX.illegal' where slotname = 'PS.base.a1';
1503 ERROR: illegal backlink beginning with XX
1504 update PSlot set slotlink = 'PS.not.there' where slotname = 'PS.base.a1';
1505 ERROR: PS.not.there does not exist
1506 update PSlot set slotlink = 'XX.illegal' where slotname = 'PS.base.a1';
1507 ERROR: illegal slotlink beginning with XX
1508 insert into HSlot values ('HS', 'base.hub1', 1, '');
1509 ERROR: Cannot insert a duplicate key into unique index hslot_name
1510 insert into HSlot values ('HS', 'base.hub1', 20, '');
1511 ERROR: no manual manipulation of HSlot
1513 ERROR: no manual manipulation of HSlot
1514 insert into IFace values ('IF', 'notthere', 'eth0', '');
1515 ERROR: system "notthere" does not exist
1516 insert into IFace values ('IF', 'orion', 'ethernet_interface_name_too_long', '');
1517 ERROR: IFace slotname "IF.orion.ethernet_interface_name_too_long" too long (20 char max)
1519 -- Test recursion, per bug report 7-Sep-01
1521 CREATE FUNCTION recursion_test(int,int) RETURNS text AS '
1525 rslt = CAST($2 AS TEXT);
1527 rslt = CAST($1 AS TEXT) || '','' || recursion_test($1 - 1, $2);
1530 END;' LANGUAGE 'plpgsql';
1531 SELECT recursion_test(4,3);