Dec 042005
 

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive