Commits against non-ports are not shown

[dlangille]

e.g. https://cgit.freebsd.org/ports/commit/?id=2f14e59e39e74f31e84f5ff674f24c0dff5b1ab0

It doesn't appear here:

https://www.freshports.org/date.php?date=2023/08/22

I know why. The SQL is designed to pull back port commits. Not commits under /usr/ports

[dlangille]

Perhaps I could use the commit_log_elements table.

[dlangille]

This works quickly:

with recent_commits AS (
select id, message_date
  from commit_log CL
ORDER BY CL.message_date desc
  LIMIT 100)
select EP.pathname, CL.message_date
  from element_pathname EP, commit_log_elements CLE, recent_commits CL
  where CL.id = CLE.commit_log_id
   AND CLE.element_id = EP.element_id
   AND EP.pathname like '/ports/%'
ORDER BY CL.message_date desc;

[dlangille]

Works in progress:

with recent_commits AS (
select CL.id, CL.message_date, CLP.port_id
  from commit_log CL LEFT OUTER JOIN 
	   commit_log_ports CLP ON CL.id = CLP.commit_log_id
ORDER BY CL.message_date desc
  LIMIT 17)
select EP.pathname, CL.message_date, port_id
  from element_pathname EP, 
       commit_log_elements CLE, 
	   recent_commits CL
  where CL.id = CLE.commit_log_id
   AND CLE.element_id = EP.element_id
   AND EP.pathname like '/ports/%'
ORDER BY CL.message_date desc;



select EP.pathname, CL.message_date
  from element_pathname EP, commit_log_elements CLE, commit_log CL
 where CL.id = CLE.commit_log_id
   AND CLE.element_id = EP.element_id
   AND EP.pathname like '/ports/%'
ORDER BY CL.message_date desc
  LIMIT 100;
  
  
with recent_commits AS (
select id as commit_log_id, message_date
  from commit_log CL
ORDER BY CL.message_date desc
  LIMIT 100)
        SELECT DISTINCT
            CL.commit_date - SystemTimeAdjust()                                                                 AS commit_date_raw,
            CL.id                                                                                               AS commit_log_id,
            CL.encoding_losses                                                                                  AS encoding_losses,
            CL.message_id                                                                                       AS message_id,
            CL.commit_hash_short                                                                                AS commit_hash_short,
            CL.committer                                                                                        AS committer,
            CL.committer_name                                                                                   AS committer_name,
            CL.committer_email                                                                                  AS committer_email,
            CL.author_name                                                                                      AS author_name,
            CL.author_email                                                                                     AS author_email,
            CL.description                                                                                      AS commit_description,
            to_char(CL.commit_date - SystemTimeAdjust(), 'DD Mon YYYY')                                         AS commit_date,
            to_char(CL.commit_date - SystemTimeAdjust(), 'HH24:MI')                                             AS commit_time,
            CLP.port_id                                                                                         AS port_id,
            C.name                                                                                              AS category,
            C.id                                                                                                AS category_id,
            E.name                                                                                              AS port,
            element_pathname(E.id)                                                                              AS element_pathname,
            CASE when CLP.port_version IS NULL then P.version  else CLP.port_version  END                       AS version,
            CASE when CLP.port_version is NULL then P.revision else CLP.port_revision END                       AS revision,
            CASE when CLP.port_epoch   is NULL then P.portepoch else CLP.port_epoch   END                       AS epoch,
            E.status                                                                                            AS status,
            CLP.needs_refresh                                                                                   AS needs_refresh,
            P.forbidden                                                                                         AS forbidden,
            P.broken                                                                                            AS broken,
            P.deprecated                                                                                        AS deprecated,
            P.ignore                                                                                            AS ignore,
            P.expiration_date                                                                                   AS expiration_date,
            date_part('epoch', P.date_added)                                                                    AS date_added,
            P.element_id                                                                                        AS element_id,
            P.short_description                                                                                 AS short_description,
            CL.svn_revision                                                                                     AS svn_revision,
            R.repo_hostname                                                                                     AS repo_hostname,
            R.repository                                                                                        AS repository,
            R.path_to_repo                                                                                      AS path_to_repo,
            R.name                                                                                              AS repo_name,
            PV.current                                                                                          AS vulnerable_current,
            PV.past                                                                                             AS vulnerable_past,
            STF.message                                                                                         AS stf_message,
            P.is_interactive                                                                                    AS is_interactive,
            P.no_cdrom                                                                                          AS no_cdrom,
            P.restricted                                                                                        AS restricted,
            SB.branch_name                                                                                      AS branch,
            NULL AS onwatchlist 
    FROM recent_commits RC JOIN commit_log_ports CLP on RC.commit_log_id = CLP.commit_log_id LEFT OUTER JOIN commit_log_branches CLB ON CLP.commit_log_id = CLB.commit_log_id
                              JOIN system_branch        SB ON SB.id = CLB.branch_id
      LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = CLP.commit_log_id, 
      (SELECT cl.*
         FROM commit_log cl
        WHERE EXISTS (select *
                        from commit_log_ports clp
                        where clp.commit_log_id = cl.id)
     ORDER BY CL.commit_date DESC, CL.id DESC
        ) AS CL LEFT OUTER JOIN repo R on CL.repo_id = R.id, categories C, ports P LEFT OUTER JOIN ports_vulnerable PV ON P.id = PV.port_id, element E 
      WHERE CLP.commit_log_id = CL.id
        AND CLP.port_id       = P.id
        AND C.id              = P.category_id
        AND E.id              = P.element_id
   ORDER BY 1 desc,
            CL.id DESC,
            category,
            port

[dlangille]

NOTES: the goal of a list of commits:

• include commits which touch only ports
• include commits which touch only non-ports (e.g. Mk)
• include commits which touch both

The WIP query has the above.

When displaying the commit details:

• show all ports involved
• show all non-port files (/Mk/ for example)

This is what I need to work on now. I have to get the right list of all elements and display the right parts.

[dlangille]

I have this. The key is the commit_log_ports_elements table and then joining everything up in the right order.

    FROM recent_commits RC
    LEFT OUTER JOIN commit_log_branches CLB  ON RC.id             = CLB.commit_log_id
    LEFT OUTER JOIN system_branch SB         ON SB.id             = CLB.branch_id
    LEFT OUTER JOIN ports P                  ON P.element_id      = clpe_element_id
    LEFT OUTER JOIN commit_log_ports CLP     ON P.id              = CLP.port_id and CLP.commit_log_id = RC.id
    LEFT OUTER JOIN element E                ON E.id              = P.element_id
    LEFT OUTER JOIN categories C             ON C.id              = P.category_id
    LEFT OUTER JOIN repo R                   on RC.repo_id        = R.id
    LEFT OUTER JOIN sanity_test_failures STF ON STF.commit_log_id = RC.id

[dlangille]

All: please check out dev. I think it's ready to merge to main and put into production.

[dlangille]

It's live now: https://news.freshports.org/2023/10/12/great-changes-to-the-websites/

[dlangille]

Updates applied. Please review.

[dlangille]

done