Now that I think about this… I’m sure there is something in the relational integrity to ensure that if a parent is marked as deleted, the children are mark deleted as well. Perhaps I need something to mark parents as active if a child is reactivated. I’ll check. In the meantime….
I couldn’t sleep, so here’s the function:
--
-- takes an element_id and checks to see if all files below it have
-- been deleted.
--
-- returns 1 if all files have been deleted
-- returns 0 if at least one file below it has not been deleted
CREATE OR REPLACE FUNCTION AllFilesDeleted(int4) returns int4 AS '
DECLARE
p_element_id ALIAS FOR $1;
queries RECORD;
l_result int;
BEGIN
FOR queries IN
SELECT id, status, directory_file_flag
FROM element
WHERE parent_id = p_element_id
LOOP
IF queries.directory_file_flag = 'F' THEN
IF queries.status = 'A' THEN
return 0;
END IF;
ELSE
l_result := AllFilesDeleted(queries.id);
IF l_result = 0 THEN
return 0;
END IF;
END IF;
END LOOP;
return 1;
END;
' LANGUAGE plpgsql STABLE;
That works rather well. And quite efficiently I might add. Now, to find ports that were deleted, but had active files, or active ports, with everything deleted, I tried this query:
SELECT P.id AS port_id,
E.id AS element_id,
C.name AS category,
E.name AS port,
E.status AS status,
AllFilesDeleted(E.id) as all_files_deleted
FROM ports P, element E, categories C
WHERE P.element_id = E.id
AND P.category_id = C.id
AND ((AllFilesDeleted(E.id) = 1 AND status = 'A') OR
(AllFilesDeleted(E.id) = 0 AND status = 'D'))
ORDER by category, port
The results are interesting. Only one active port with deleted files, but over 200 deleted ports that have one or more active files:
freshports.org=# SELECT P.id AS port_id,
freshports.org-# E.id AS element_id,
freshports.org-# C.name AS category,
freshports.org-# E.name AS port,
freshports.org-# E.status AS status,
freshports.org-# AllFilesDeleted(E.id) as all_files_deleted
freshports.org-# FROM ports P, element E, categories C
freshports.org-# WHERE P.element_id = E.id
freshports.org-# AND P.category_id = C.id
freshports.org-# AND ((AllFilesDeleted(E.id) = 1 AND status = 'A') OR
freshports.org(# (AllFilesDeleted(E.id) = 0 AND status = 'D'))
freshports.org-# ORDER by category, port
freshports.org-# ;
port_id | element_id | category | port | status | all_files_deleted
---------+------------+--------------+------------------------------+--------+-------------------
1299 | 6187 | audio | bwap | D | 0
2845 | 17424 | audio | vorbis | D | 0
4139 | 45089 | chinese | XFree86-aoutlibs | D | 0
4609 | 51739 | converters | ruby-sha1 | D | 0
3005 | 20410 | converters | tcs | D | 0
6319 | 70149 | databases | db3-latest | D | 0
4504 | 49666 | databases | mysql322-client | D | 0
1533 | 7385 | databases | mysql322-server | D | 0
1552 | 7388 | databases | postgresql | D | 0
2770 | 16815 | databases | ruby14-gdbm | D | 0
2865 | 17522 | deskutils | bitnotes | D | 0
377 | 2081 | deskutils | gnucash | D | 0
3013 | 20552 | deskutils | korganizer | D | 0
4943 | 56074 | deskutils | phpgw_inventory | D | 0
4944 | 56077 | deskutils | phpgw_projects | D | 0
2044 | 11405 | deskutils | wpeople | D | 0
2729 | 16595 | devel | binutils-m68k | D | 0
4588 | 51382 | devel | cpp2html | D | 0
2732 | 16597 | devel | crossm68k | D | 0
4641 | 52194 | devel | elib-emacs | D | 0
2733 | 16599 | devel | gcc-m68k | D | 0
2734 | 16601 | devel | gdb-m68k | D | 0
2847 | 17429 | devel | gtksheet | D | 0
1938 | 10433 | devel | idle | D | 0
2329 | 13297 | devel | libsock | D | 0
2327 | 13305 | devel | libstash | D | 0
2516 | 14858 | devel | py-mxDateTime | D | 0
4589 | 51385 | devel | py-mxProxy | D | 0
2519 | 14860 | devel | py-mxStack | D | 0
2517 | 14862 | devel | py-mxTools | D | 0
3045 | 21036 | editors | apel-emacs | D | 0
1736 | 8797 | editors | apel-xemacs | D | 0
1738 | 8800 | editors | apel-xemacs20 | D | 0
2160 | 11794 | editors | codecommander | D | 0
1757 | 8813 | editors | flim-emacs-current | D | 0
1628 | 8098 | editors | flim-emacs20-current | D | 0
1744 | 8830 | editors | flim-mule-current | D | 0
1755 | 8834 | editors | flim-xemacs | D | 0
1759 | 8838 | editors | flim-xemacs-current | D | 0
1745 | 8842 | editors | flim-xemacs20 | D | 0
1741 | 8846 | editors | flim-xemacs20-current | D | 0
1638 | 8114 | editors | flim-xemacs21-mule-current | D | 0
6970 | 77340 | editors | openoffice-devel | D | 0
1763 | 8934 | editors | psgml-emacs | D | 0
1753 | 8850 | editors | semi-emacs | D | 0
1751 | 8858 | editors | semi-emacs-current | D | 0
1634 | 8104 | editors | semi-emacs20-current | D | 0
1756 | 8870 | editors | semi-mule | D | 0
1761 | 8874 | editors | semi-mule-current | D | 0
1754 | 8878 | editors | semi-xemacs | D | 0
1752 | 8882 | editors | semi-xemacs-current | D | 0
1762 | 8886 | editors | semi-xemacs20 | D | 0
1758 | 8890 | editors | semi-xemacs20-current | D | 0
1636 | 8120 | editors | semi-xemacs21-mule-current | D | 0
2858 | 17485 | editors | vim6 | D | 0
1376 | 6362 | editors | wemi-emacs | D | 0
1746 | 8900 | editors | wemi-emacs-current | D | 0
1630 | 8108 | editors | wemi-emacs20 | D | 0
1631 | 8110 | editors | wemi-emacs20-current | D | 0
1747 | 8910 | editors | wemi-mule | D | 0
1749 | 8914 | editors | wemi-mule-current | D | 0
1743 | 8918 | editors | wemi-xemacs | D | 0
1740 | 8922 | editors | wemi-xemacs-current | D | 0
1750 | 8926 | editors | wemi-xemacs20 | D | 0
1748 | 8930 | editors | wemi-xemacs20-current | D | 0
1771 | 8990 | editors | wemi-xemacs21-mule | D | 0
1772 | 8994 | editors | wemi-xemacs21-mule-current | D | 0
1475 | 7156 | editors | xemacs20 | D | 0
6938 | 76551 | french | linux-netscape6 | D | 0
1535 | 7404 | games | bnetd | D | 0
3456 | 28365 | games | linuxdoom | D | 0
2065 | 11463 | games | xsheep | D | 0
6937 | 76537 | german | linux-netscape6 | D | 0
751 | 3748 | german | spinne | D | 0
2211 | 12215 | german | webalizer | D | 0
756 | 3754 | graphics | hpscan | D | 0
4556 | 50667 | graphics | imlib2-tmp | D | 0
5433 | 60922 | graphics | iview | D | 0
2190 | 12040 | graphics | killustrator | D | 0
3183 | 24449 | graphics | kplot3d | D | 0
1486 | 7162 | graphics | pnmtopng | D | 0
911 | 4497 | graphics | sane | D | 0
555 | 2801 | graphics | tgd | D | 0
4327 | 48025 | graphics | xmms-gforce | D | 0
5084 | 57420 | irc | liece-emacs20 | D | 0
1003 | 4934 | japanese | Wnn | D | 0
453 | 2343 | japanese | gnome-icu | D | 0
4029 | 43611 | japanese | kebook | D | 0
6939 | 76553 | japanese | linux-netscape6 | D | 0
4278 | 47412 | japanese | pips2200 | D | 0
4277 | 47415 | japanese | pips3000 | D | 0
4279 | 47418 | japanese | pips3300 | D | 0
4275 | 47421 | japanese | pips670 | D | 0
4276 | 47424 | japanese | pips750_2000 | D | 0
3246 | 26869 | japanese | pips760 | D | 0
3256 | 26874 | japanese | pips770 | D | 0
3320 | 26879 | japanese | pips800 | D | 0
3333 | 26884 | japanese | pips820_3300 | D | 0
4274 | 47431 | japanese | pips900 | D | 0
2443 | 14436 | japanese | ruby14-tcltklib | D | 0
2441 | 14438 | japanese | ruby14-tk | D | 0
2064 | 11500 | japanese | wine | D | 0
722 | 3768 | japanese | xfig | D | 0
1733 | 8768 | korean | ftghostscript55 | D | 0
3552 | 31704 | lang | gcc-devel | D | 0
2255 | 12612 | lang | irb | D | 0
7343 | 91738 | lang | librep2 | D | 0
753 | 3770 | lang | mocka | D | 0
3581 | 31984 | lang | modula-3 | D | 0
729 | 3772 | lang | modula-3-lib | D | 0
3572 | 32027 | lang | modula-3-socks | D | 0
4528 | 50253 | lang | php | D | 0
5220 | 58749 | lang | python20 | D | 0
2438 | 14420 | lang | ruby14 | D | 0
2771 | 16819 | lang | ruby14-irb | D | 0
2446 | 14448 | lang | ruby14-tcltklib | D | 0
2231 | 12386 | mail | pine4-ssl | D | 0
2772 | 16821 | mail | ruby-net-imap | D | 0
3493 | 30314 | math | Scilab | D | 0
847 | 4226 | math | numpy | D | 0
246 | 1390 | math | rng | D | 0
3672 | 33074 | misc | 221upgrade | D | 0
3665 | 33081 | misc | 222upgrade | D | 0
3645 | 33088 | misc | 225upgrade | D | 0
3629 | 33095 | misc | 227upgrade | D | 0
2353 | 13575 | misc | 30upgrade | D | 0
2354 | 13579 | misc | 31upgrade | D | 0
2351 | 13583 | misc | 32upgrade | D | 0
2352 | 13587 | misc | 34upgrade | D | 0
892 | 4413 | misc | amanda24 | D | 0
2295 | 13014 | misc | gweather | D | 0
3631 | 33542 | misc | kdeutils11 | D | 0
986 | 4760 | misc | kdeutils2 | D | 0
4686 | 53022 | misc | khotkeys | D | 0
3652 | 33824 | misc | sma | D | 0
2830 | 17376 | net | cvsup-bin | D | 0
5344 | 60089 | net | cvsup-devel | D | 0
2832 | 17378 | net | cvsupd-bin | D | 0
727 | 3794 | net | dip | D | 0
2236 | 12392 | net | dnscache | D | 0
1546 | 7465 | net | elsa | D | 0
384 | 2135 | net | gnome-icu | D | 0
2851 | 17459 | net | gnubile | D | 0
1891 | 10064 | net | isc-dhcp2 | D | 0
4629 | 52060 | net | jabber | D | 0
989 | 4772 | net | libetherx | D | 0
970 | 4780 | net | libjabber | D | 0
1371 | 6431 | net | netatalk-asun | D | 0
1116 | 5158 | net | p5-pRPC | D | 0
1353 | 6435 | net | ppp-nt | D | 0
6288 | 69845 | net | wmqmon | D | 0
404 | 2144 | palm | gnomepilot | D | 0
3709 | 34107 | print | a2ps43-letter | D | 0
1453 | 6996 | print | ghostscript5 | D | 0
1469 | 7179 | print | ghostscript55 | D | 0
1484 | 7181 | print | ghostscript6 | D | 0
5422 | 60802 | print | ghostscript6-nox11 | D | 0
2758 | 16763 | print | pbm2ppa | D | 0
2783 | 16912 | print | pips820_3300 | D | 0
4053 | 43911 | security | hping | D | 0
939 | 4803 | security | librc4 | D | 0
974 | 4807 | security | librsaintl | D | 0
5282 | 59480 | security | ruby-sha1 | D | 0
4608 | 51767 | security | ruby-ssl | D | 0
2416 | 14127 | security | uvscan | D | 0
2297 | 13021 | sysutils | cdrecord | D | 0
9394 | 137452 | sysutils | gok | D | 0
2276 | 12819 | sysutils | ruby-syslog | D | 0
772 | 3890 | textproc | de-ispell | D | 0
767 | 3892 | textproc | dealt-ispell | D | 0
773 | 3894 | textproc | deneu-ispell | D | 0
771 | 3896 | textproc | fr-ispell | D | 0
3788 | 36862 | textproc | mswordview | D | 0
1386 | 6542 | textproc | pspell-ispell | D | 0
2521 | 14866 | textproc | py-mxTextTools | D | 0
1476 | 7191 | www | Mosaic | D | 0
2809 | 17186 | www | bsdi-netscape47-communicator | D | 0
3934 | 41099 | www | bsdi-netscape47-navigator | D | 0
4590 | 51423 | www | junkbuster-zlib | D | 0
1199 | 5610 | www | kdreamsite | D | 0
1195 | 5612 | www | kwebsearch | D | 0
3935 | 41427 | www | linux-netscape6 | D | 0
4563 | 50853 | www | mnoGoSearch | D | 0
4591 | 51426 | www | mnoGoSearch-current | D | 0
2602 | 15495 | www | mozilla+ipv6 | D | 0
1085 | 5192 | www | p5-Apache | D | 0
1547 | 7535 | www | squid22 | D | 0
1548 | 7539 | www | squid23 | D | 0
2123 | 11562 | www | vrweb | D | 0
550 | 2812 | www | webalizer-2 | D | 0
3920 | 39513 | x11 | kde11 | D | 0
3891 | 39602 | x11 | kworldwatch | D | 0
4691 | 53098 | x11 | ruby-gnome | A | 1
2260 | 12684 | x11 | ruby-kde-i18n | D | 0
74 | 429 | x11-clocks | kdetoys2 | D | 0
92 | 517 | x11-fm | gnomemc | D | 0
310 | 1856 | x11-fonts | bitmap-font | D | 0
1022 | 5014 | x11-toolkits | gtk10 | D | 0
815 | 4105 | x11-toolkits | gtkstep-pastel | D | 0
953 | 4846 | x11-toolkits | kde-qt-addon | D | 0
4914 | 55670 | x11-toolkits | qt2-static | D | 0
758 | 3817 | x11-toolkits | qt20 | D | 0
929 | 4564 | x11-toolkits | qt21 | D | 0
1973 | 10833 | x11-toolkits | qt22 | D | 0
2252 | 12597 | x11-toolkits | ruby-qt-i18n | D | 0
2442 | 14463 | x11-toolkits | ruby14-tk | D | 0
2194 | 12086 | x11-toolkits | rubytk | D | 0
864 | 4252 | x11-wm | icewm-i18n | D | 0
468 | 2412 | x11-wm | sawmill | D | 0
883 | 4378 | x11-wm | windowmaker-i18n | D | 0
(210 rows)
freshports.org=#
Now… I should write some code to fix up these things. First, to mark the active port as deleted. Second, to mark as deleted, all the the files under the deleted ports.
But not tonight. Now I’m ready for sleep. Nite.
