Notification: New Package Available

jasker5183 commented 8 months ago

Currently Freshports can send out email notifications when there are changes to ports on your watch lists but there are cases where the ports don't make it into the actual repo for up to a week or more. I think it would be nice if you could instead set it to monitor the actual ABI repository (eg. FreeBSD:13:amd64, FreeBSD:14:amd64, etc) and get email notifications when there are changes there.

dlangille commented 8 months ago

For FreshPorts to tell you that a new package is available for something on your watch list, the following needs to exist (what follows is mostly notes for myself)

item on your watch list (port.id)
A link from the packages table to the port (packages.port_id)
A timestamp telling us when that entry was added to the packages table (package_imports.processed_date)

I think a report can be generated from the existing data without database modification.

This would be a new report (new package available) and would use existing watch lists.
The hardest part would be figuring out the correct query.

dlangille commented 8 months ago

This new feature requires careful consideration. I've been thinking about it over the past few days.

We need to define 'new package'.

It is not enough that a new repo build is available (I'll also refer to that as a 'new build', 'import', or 'repo' - they all mean the same thing). We have that information already. The user does not want to be notified that a new build is available. They don't care if it's the same version as the last build. "Hey, user, foo-1.3 has just been built" is not useful if you have foo-1.3 installed.

I think the objective is knowing that the package version/revision has been modified. That sounds like a trigger to me (the trigger would add that port to a list). As a new repo build is imported into FreshPorts, the triggers will build list of 'new package' instances. After the import is done, emails will go out.

I see 'new package' notifications should go out immediately upon the above import. Why?

it is cleaner to implement
the users get notifications ASAP

If a user is watching multiple ABI, they might get multiple emails per day. If they are watching just one ABI, they'll get at most one email per new.

dlangille commented 8 months ago

NOTES on table structures:

package_imports contains stats on all imports ever done - current row count - 2821
users will need to select ABI and latest / quarterly for that ABI
I imagine some will want to select both latest & quarterly for that ABI

The latest import for FreeBSD:14:amd64 on latest:

(see https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT)

freshports.dvl=# with latest_imports as
(SELECT distinct on (abi_id, package_set) *
FROM public.package_imports
order by abi_id, package_set)
select abi.name, LI.package_set, LI.processed_date
from latest_imports LI join abi on LI.abi_id = abi.id and abi.name = 'FreeBSD:14:amd64' and LI.package_set= 'latest'
join packages P on P.abi_id = LI.abi_id and P.package_name = 'bacula13-server' and P.package_set = 'latest';
       name       | package_set |        processed_date         
------------------+-------------+-------------------------------
 FreeBSD:14:amd64 | latest      | 2023-12-23 22:11:19.165986+00
(1 row)

freshports.dvl=#

The current idea for a table:

-- Table: public.package_notifications

-- DROP TABLE IF EXISTS public.package_notifications;

CREATE TABLE IF NOT EXISTS public.package_notifications
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    abi_id integer NOT NULL,
    package_set package_sets NOT NULL,
    port_id integer NOT NULL,
    action action NOT NULL,
    version_previous text COLLATE pg_catalog."default",
    version_current text COLLATE pg_catalog."default",
    CONSTRAINT package_notifications_pkey PRIMARY KEY (id),
    CONSTRAINT package_notifications_abi_id FOREIGN KEY (abi_id)
        REFERENCES public.abi (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT package_notifications_port_id_fk FOREIGN KEY (port_id)
        REFERENCES public.ports (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT package_notifications_action_check CHECK (action = 'insert'::action AND version_previous IS NULL AND version_current IS NOT NULL OR action = 'update'::action AND version_previous IS NOT NULL AND version_current IS NOT NULL OR action = 'delete'::action AND version_previous IS NOT NULL AND version_current IS NULL) NOT VALID
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.package_notifications
    OWNER to postgres;

COMMENT ON TABLE public.package_notifications
    IS 'when a new repo is imported, we need to notify users. This table lets us build a list of what has change in this repo.

re: https://github.com/FreshPorts/freshports/issues/542';

COMMENT ON COLUMN public.package_notifications.action
    IS 'Was this row the result of an insert, update, or a delete on the packages table?';

COMMENT ON CONSTRAINT package_notifications_action_check ON public.package_notifications
    IS 'read the constraint as:

        (action = ''insert''::action AND version_previous IS NULL AND version_current IS NOT NULL)
        OR (action = ''update''::action AND version_previous IS NOT NULL AND version_current IS NOT NULL)
        OR (action = ''delete''::action AND version_previous IS NOT NULL AND version_current IS NULL))
';

Subscriptions to a particular ABI are noted through this table:

--DROP TABLE IF EXISTS public.report_subscriptions_abi;

CREATE TABLE IF NOT EXISTS public.report_subscriptions_abi
(
    user_id integer NOT NULL,
    abi_id integer NOT NULL,
    watch_list_id integer NOT NULL,
    CONSTRAINT report_subscriptions_abi_user_abi_watch_pk UNIQUE NULLS NOT DISTINCT (user_id, abi_id, watch_list_id),
    CONSTRAINT report_subscriptions_abi_abi_id FOREIGN KEY (abi_id)
        REFERENCES public.abi (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT report_subscriptions_abi_user_id FOREIGN KEY (user_id)
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT report_subscriptions_abi_watch_list_id FOREIGN KEY (watch_list_id)
        REFERENCES public.watch_list (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.report_subscriptions_abi
    OWNER to postgres;

COMMENT ON TABLE public.report_subscriptions_abi
    IS 'Records the ABI a given user follows - relates to package_notifications table

You can associate a given watch list with one or more ABI.';

COMMENT ON COLUMN public.report_subscriptions_abi.watch_list_id
    IS 'We don''t need user_id, because watch_list_id will find us the user_id. We duplicate that information here because I think it''ll be useful in queries.';

COMMENT ON CONSTRAINT report_subscriptions_abi_user_abi_watch_pk ON public.report_subscriptions_abi
    IS 'For a given user_id, abi_id, and watch_list_id must be unique.';

Adding the new report to the database:

INSERT INTO public.reports(
    name, description, needs_frequency)
    VALUES ('New Package Notification', 'Notification when a new package is available for something on your watch list.', false);

During a walk last night, this idea came to mind:

Add three more columns to the package_notifications table:

action (one of insert, update, or delete)
version_previous: text
version_current: text
For insert : version_previous will be null; version_current must be not null
For update : both version_previous and version_current must be not null
For delete : version_previous must be null , and version_current must be null

Testing data:

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'update', '13.0.3_1', '13.0.3_2');

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'insert', null, '13.0.3_1');

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'delete', '13.0.3_1', null);

-- failures

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'update', '13.0.3_1', null);

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'update', null, '13.0.3_1');

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'insert', '13.0.3_1', null);

insert into package_notifications(abi_id, package_set, port_id, action, version_previous, version_current)
values (27, 'latest', 101597, 'delete', null, '13.0.3_1');

dlangille commented 8 months ago

This is how the report subscriptions page will change:

report-mockup

dlangille commented 8 months ago

first draft: selection of ABI for each watch list

abi-selection

dlangille commented 8 months ago

This is the user-interface idea I had last night:

display list of ABI (e.g. FreeBSD:14-amd64, FreeBSD:13:amd64)
display list of watch lists (e.g. foo, bar, things)
allow multiple clicks on both lists
when done clicking, click on ADD
ADD will add the combinations of ABI and watch list to the list of package notifications which now contains: FreeBSD:14-amd64 foo FreeBSD:14-amd64 things ** FreeBSD:13:amd64 bar

dlangille commented 8 months ago

I've asked for help:

dlangille commented 8 months ago

Here's some content: full listing at https://gist.github.com/dlangille/80e4b7169ac5d6c5d9cc2e9941f4b223


freshports.dvl=# select PA.category || '/' || PA.name as port, abi.name as abi, PN.package_set as set, pn.action, 
pn.version_previous, pn.version_current from package_notifications PN join ports_active PA on PA.id = PN.port_id join 
abi on ABI.id = PN.abi_id order by abi, set, port;
                           port                            |        abi         |    set    | action |     version_previous      |      version_current      
-----------------------------------------------------------+--------------------+-----------+--------+---------------------------+---------------------------
 accessibility/kmag                                        | FreeBSD:13:amd64   | latest    | update | 23.08.4_1                 | 23.08.4_2
 accessibility/kmousetool                                  | FreeBSD:13:amd64   | latest    | update | 23.08.4                   | 23.08.4_1
 accessibility/kmouth                                      | FreeBSD:13:amd64   | latest    | update | 23.08.4                   | 23.08.4_1
 accessibility/py-speech-dispatcher                        | FreeBSD:13:amd64   | latest    | update | 0.11.2_3                  | 0.11.2_4
 accessibility/speech-dispatcher                           | FreeBSD:13:amd64   | latest    | update | 0.11.2_3                  | 0.11.2_4
 archivers/R-cran-zip                                      | FreeBSD:13:amd64   | latest    | update | 2.3.0_1                   | 2.3.1
 archivers/arc                                             | FreeBSD:13:amd64   | latest    | update | 5.21q                     | 5.21q_1
 archivers/arj                                             | FreeBSD:13:amd64   | latest    | update | 3.10.22_11                | 3.10.22_12
 archivers/ark                                             | FreeBSD:13:amd64   | latest    | update | 23.08.4                   | 23.08.4_1
 archivers/ark-devel                                       | FreeBSD:13:amd64   | latest    | insert |                           | 24.01.90_1
 archivers/bzip2                                           | FreeBSD:13:amd64   | latest    | update | 1.0.8                     | 1.0.8_1

dlangille commented 8 months ago

HTML as it stands now: https://gist.github.com/dlangille/9a8539c8edeb04a86046bfd5ba57692f