remove duplicate but different instance of FUNCTION public.ports_categories_set() RETURNS trigger

Adjust column aligment for better reading

Adjust column aligment for better reading

mv updates-2024-06-21.txt updates-2024-06-22.README.txt

Move users.cookie to a new table: user_cookie

This allows multiple logins for different sessions, say
from your desktop and from your laptop.

re: https://github.com/FreshPorts/freshports/issues/574

for package_notifications table, use package_notifications_abi_set_port_idx CONSTRAINT

Pulled in all the changes from the DB via PGAdmin 8.4

GRANT INSERT                 ON packages_last_checked TO abi_maintenance

first draft of triggers for package_notifications table

add update to abi_maintenance

For PackagesLastCheckedSetRepoDate(), indicate an update is required only if a_RepoDate
is not null. If it is null, there is no such repo and it cannot be imported.

See: https://news.freshports.org/2023/12/31/no-repos-need-importing-how-did-this-happen-this-should-never-happen/

Add more comments

Add new function: DoneProcessingCommit()

It is not invoked anywhere - I suspect that would
be somewhere inside the commit inserting/adding
code.

Remove unneeded declarations and notices.

Remove latest_link, no longer used.

When creating a port on a branch, use the ports table, not the ports_active
view to get the element_id. It won't be there because ports_active is now
head only.

re: https://github.com/FreshPorts/freshports/issues/413
    https://github.com/FreshPorts/freshports/issues/407

Add test_depends to views.

make both ports_all and ports_active head-specific.

We shall see if this works out OK

re: https://github.com/FreshPorts/freshports/issues/407
    https://github.com/FreshPorts/freshports/issues/406

When clearing cache for a port, consider all categories

re https://github.com/FreshPorts/freshports/issues/373

A port  has a primary category. e.g. x11/bemenu

However, this port is also listed in the wayland category,
which is a virtual category. This is known as a secondary
category. Secondary categories are  always vritual.

You can't browse to wayland/bemenu - but you can see bemenu
listed at https://www.freshports.org/wayland/

Therefore, we must flush that page from cache when that port
is updated.

The solution: insert both of these pairs into the cache_clearing_ports
table:

* x11,bemenu
* wayland,bemenu

The fp-listen daemon, which runs on the webserver nodes, will always clear
the category page in addition to  the port page.

It will also clear the now non-existent wayland/bemenu page - no harm done
there. Should the display of a port on a virtual page be implemented one
day, this solution will properly clear that cache too.

Fix a trigger optimization which avoided updating the table if the values
did not change.

re: https://news.freshports.org/2022/08/19/missing-ports_categories-entries/

don't tell me about required modifying the the parent % from a file to directory

better comment

Make sure the branch is in public.freshports_commit_record - fixes https://github.com/FreshPorts/freshports/issues/327

silence some entries.

return multiple records when searching for elements

Add pkg_plist()

comment out conflicts type so we do not lose a table

add author and committer name and email

commiter -> committer

better suggestion from Magnus

This is the first attempt at converting expression which one ran on a single column
into one which runs on rows.

Previously, it ran on array_to_json(regexp_match(ports.pkg_plist, 'lib/[[:alpha:]]*?\.so'))

But now those values are speard across several rows in the generate_plist
table.

This first attempt is wrong.

Comment out some NOTICE and WARNINGS

use CREATE OR REPLACE FUNCTION instead of CREATE FUNCTION

ZTHEN -> THEN

Remove trailing ;

Add in the changes from the subversion branch.

Use -- instead of # for comments

Remove AddNewABIToPackagesLastChecked() which does not compile against the packages_last_checked
table which does not contain a column named branch_id

Add stuff for updating the database for use by git

Fix this up for devgit - it wasn't all right

Copying in things added to the sp branch which were never on the git branch

spaces onlyi

third draft of elementGetCaseInsensitive()

first draft of elementGetCaseInsensitive()

Add more notices

If there is nothing to save in port_dependencies_delete_clear_cache(),
don't save it.  It avoids this situation:

freshports.devgit=# delete from element where id = 1058393;
ERROR:  null value in column "category" violates not-null constraint
DETAIL:  Failing row contains (19934459, 59842, null, null, 2020-08-02 21:50:23.967785).
CONTEXT:  SQL statement "INSERT INTO cache_clearing_ports (port_id, category, port)
              VALUES (OLD.port_id, l_category, l_port)"
PL/pgSQL function port_dependencies_delete_clear_cache() line 19 at SQL statement
SQL statement "delete from element 
   where parent_id=old.id"
PL/pgSQL function on_delete_remove_children() line 4 at SQL statement
SQL statement "delete from element 
   where parent_id=old.id"
PL/pgSQL function on_delete_remove_children() line 4 at SQL statement
SQL statement "delete from element 
   where parent_id=old.id"
PL/pgSQL function on_delete_remove_children() line 4 at SQL statement
freshports.devgit=# rollback;

svn_hostname => repo_hostname

Branch for git

Fix  GetCategoryPortFromLatestLink() which did not work well with branches.

This fixes:

* https://dev.freshports.org/commits.php?package=mariadb55-server
* https://dev.freshports.org/?package=mariadb55-server
* https://dev.freshports.org/filter.php?package=mariadb55-server
* https://dev.freshports.org/vuxml.php?vid=198e6220-ac8b-11e9-a1c7-b499baebfeaf

re https://github.com/FreshPorts/freshports/issues/124

fix PortVersionLatestBeforeQuarterlyBranch which was not pulling back the
full version information

Move some inserts into another ddl update file.

This one to be run after ri.txt

Move iso_date() and iso_date_seconds() above where they are used.

add GetPackageStatus() and iso_date()

Properly save away the package_raw update code.

See http://news.freshports.org/2020/04/28/found-new-repo-release-automatically-imported-it

PackagesLastCheckedSetRepoDate() needs to grab the pre-update value of repo_date for comparison

Rewrite PackagesLastCheckedSetRepoDate to clear processed_date & import_date
if the new repo_date is not the same as the incoming date.

If the dates are the same, no update occurs.

Returns the number of rows updated.

Update packages_raw by joining with ports and element_pathname

Adjust names for some functions to better describe what they do.

fix packages_last_checked to use package_sets

populate public.abi

when a new row goes into abi, add two rows into packages_last_checked

Rename trigger and function ports_origin_insert to ports_origin_maintain
because it's run on both update and insert, not just insert.

Use IS DISTINCT FROM on the update.  

Add in the real PortPackages

Add ABI table

Whitespace for element_pathname_update

Change ports_origin_insert to pull port_origin first

Then do the UPDATE only if things have changed - no need to churn

keep the ports_origin table up to date

when package_imports is updated, note that in the packages_last_checked
table

a bunch of ' on ' to ' ON ' etc

Add UpdatePackagesFromRawPackages()

DISTINCT doesn't need ()
sort_key is no longer required

We got progress

Expand PortPackages to pull back dates from packages_last_checked

Add AddNewABIToPackagesLastChecked()

This should be invoked each time a new row is added to the ABI table.

Making good progress on packages

Add DeleteUser

Delete by user id as supplied by the code.
The initial intent was to delete by cookie, but that
value is on the client.  Let's do this by id instead.

SECURITY DEFINER - means run this function as the user
                   which created. This means the www
                   user does not require write permission
                   on the users table.

Whitespace only

use = not ==

Fix new element insertion failures on PostgreSQL 12

Was getting this:

select Element_Add('/ports/head/www/httpdirfs/', 'F');

ERROR:  duplicate key value violates unique constraint "element_pathname_pathname" DETAIL:  Key (pathname)=() already exists.


Because:

      INSERT INTO element_pathname (element_id, pathname)
         VALUES (NEW.id, element_pathname(NEW.id));

The pathname value was always NULL.

The fix, change the SQL to:

   DECLARE
     l_pathname  text;
   BEGIN
     l_pathname := element_pathname(NEW.id);
     INSERT INTO element_pathname (element_id, pathname) 
         VALUES (NEW.id, l_pathname);

Add Quarterly revision

* fixes #115
* display the port version on the latest quarterly branch

Add sort-by last updated

* fixes #116

Remove stuff no long relevent
Add mention of sudoers.

whitespace only

rsyncer must be able to dump all tables

do not drop TYPE conflicts

Change DROP TRIGGER to DROP TRIGGER IF EXISTS

Add the tables and functions for package flavors

Add package flavors

Correct CategoryPortCount() so it uses the incoming parmeter, not a
hardcoded 'databases' value.

This fixes: https://github.com/FreshPorts/freshports/issues/67

Add: CREATE TRUSTED PROCEDURAL LANGUAGE plperl

Add permissions for CONFLICTS

Save work in progress

remove cruft

When inserting dependencies, ignore duplicates.

re: https://www.freshports.org/commit.php?category=net&port=ceph&files=yes&message_id=201803060059.w260xMYN015292@repo.freebsd.org
    https://news.freshports.org/2018/03/11/duplicate-dependency-issues-avoiding-duplicate-rows/

Add port_dependencies_clear_cache()

Correctly count on head: Restricted, NO CDROM, and Vulnerable

Correctly count deprecated on head

Make the count of expired and expiring ports branch aware.

Make WatchListStagingProcess operate upon HEAD only

When deleting from ports_vulnerable the port might
already be deleted.  Cater for that.

Correct filename

Better cache clearing

Add pkg_plist & makefile

distinfo stuff

whitespace

The stored procedure GetGategory($CategoryName) did not work so I changed it
to prefix the category name with 'ports/head/' instead of just 'ports/'.
It seems this function was not used anywhere, but at least now it works.

Add a bunch of debugging for processing UPDATING
add repo.name to a query or two

Add entry to listen_for table

Comment out a RAISE notice

REturn repo.name

add trailing newline

Update for branches

Fix updates to revision name

How to use jails

Moving to full pathnames, including repo, on commits

Add revision

Add new column to vuxml table for checksum
Use this to see if a vuln changes.

Correct the calculations of vulnerabilities by not using ports_vulnerable
table.  That's what we're setting up now. Don't use it.

When rows are deleted from ports_vulnerable, be sure to clear the cache

Add the stored procedure for PortsDependenciesAdd()

Add port_dependencies

Add password reset stuff

Put the bracket ( in the right place.

convert to $$ for procedures

Cast the first arguent to digest().
Under 8.3.3 it seems to need this.

Add ports.showconfig, which will be the output of make showconfig

Add triggers to keep watch list count up to date as people add/change
entries to/from their watch lists.

Add the master_slave() table, as yet unused.
See http://news.freshports.org/2007/10/12/masterslave-relationships/

Sanity Test Failures should have a delete cascade, not delete restrict,
relationship with commit_log

Alter the announcements table to have plain text.

for the design_results table

Instructions for daemontools/fp-listen/config.py

instructions for adding processing of www/en/ports/categories for fp-listen

Merge from FreshPorts2 to HEAD

Add commit_group and commit_group_contents

Add changes to element_type

Add element_pathname to element_type

Add more capacity to the daily_stats_table by using bigint.

Add Stats_DatabaseSize
Order output by good stuff.

fix broken command

Take only what's in the user's staging area, not the whole staging area.

Reported by Christian Ullrich <chris@chrullrich.de>

Add SanityTestFailures(int)

Populate element_pathname

Add MigrateStagingAreaToWatchList()

Give us a PDF version of the tables.

Remove this one too.

Add CategoryStatsUpdate()

Correct a trigger name

Add LatestCommitsFiltered
and LatestCommitsLargeFiltered.
Remove security_notice.

Add triggers for maintaining element_pathname

A late check of data.

new tables for cross platform and cross arch testing of ports:
releases    - what releases have we had?
archs       - what archs do we have?
port_status - status of a given port with respect to release and arch

add fields to ports table already in production:
- is_interactive
- only_for_archs
- not_for_archs

For person watch lists on watch_list, create a token
using generate_watch_list_token()

For filtering, add new table element_pathname

EOF.

Remove the security notice table

Add two new functions:

- Element_ID(category, port)
- OnWatchList(UserID, ElementID)

fix Port_ID() and put a condition on the ports.category_id field.
This avoids duplicates when a port is in more than one category.
For example, mail/squirrelmail which is also listed in www.

Add WatchListDeletedPorts(bigint) for catering to ports on your watch list which
have been deleted from the ports tree
Add Port_ID() which gives you the port id for a category/port combination.  This
function handles virtual cagtegories.

Found this problem when using PG8.

l_SlavePortCategory and l_SlavePortName must be declared.

Add AllFilesDeleted() and AnyActiveFiles().

Add three new columns to the ports table:
	is_interactive
	only_for_archs
	not_for_archs

Add GMT_Format()

Add expiration_date

Move the index further down.

Add expiration date

add Stats_PortCountVulnerable(

Add restricted and no_cdrom fields to the ports table

Add restricted and no_cdrom fields to LatestCommits* functions

Add vulnerable_current and vulnerable_past

remove ClearSlavePort (not used)

Include instructions for setting up ports_vulnerable

Add PortsVulnerabilityCountAdjust() which sets the correct values in the
ports_vulnerable table.

divide LatestCommits into LatestCommitsSmall and LatestCommitsLarge which
deal with 10 and 100 commits respectively.  This dramatically improves
the speed.

Comment out a notice

Convert to using timestamp without timezone in anticipation of going to UTC
internally.
Add index on commit_log_ports_port_id on commit_log_ports (port_id);

Add vuxml_ranges_vid()
Add TITLE to WatchersAlsoWatched()

Add some vuxml functions expected to be used when I start optimizing
the vuxml process.
Add currenttimestamputc() that may be useful when moving to UTC
Add WatchersAlsoWatched() to display "People watching this port, also watch".

This commit was manufactured by cvs2svn to create tag 'FreshPorts-VuXML'.

Add the vuxml tables

Start using epoch

Add vuxml_record

vuxml_ranges.vuxml_name_id becomes vuxml_affected_id.

Add package_version(text, text, text) RETURNS text

This function strings together PortVersion, PortRevision, and PortEpoch
as they are for a package name.

add port_epoch to commit_log_ports
rename ports.pkgname to ports.package_name

-    is_slave_port              text                          ,
+    master_port                text                          ,
     latest_link                text                          ,
+    depends_lib                text                          ,
     no_latest_link             text                          ,
     no_package                 text                          ,
     pkgname                    text                          ,
+    portepoch

Rename is_slave_port to master_port to reflect what we are actually using in the
database.
Add depends_lib and portepoch.

Alter GetCategoryPortFromLatestLink() to remove RAISE NOTICE debugging
messages.  Also if more than one match found, return -1, not the count.
This makes the return value more useful.

Add GetCategoryPortFromLatestLink(text)

add PortsUpdatingPortsXrefAdd

Add is_slave_port and latest_link to the ports table

add comments

add security_notice_id to freshports_commit result set

Add forbidden, deprecated, ignore, and broken.

Alter freshports_commit(text) to take a page size and offset.
Add freshports_commit_count_elements(text) which returns the number of rows.

handle the new ports.deprecated field

rename status to security_notice_status_id

upgrade instructions for security_notice*

Create security_notice_status

Remove shadow introduced via CONTROL-W

Provide instructions for latest changes to page_load_summary

Better explanations about commit_log_ports_elements

Add function AnnouncementsGet

In the page_load_summary table:
   remove defaults for user and non_users
   rename non_users to total
   add rendering_time_min and rendering_time_max

In the page_load_detail table:
   split timestamp into two fields: date and time.
   remove NOT NULL on user_id column

in page_load_summary:
  - rename name to page_name
  - add rendering_time_min and rendering_time_max

in page_load_detail
  - break timestamp into date and time fields
  - user_id can be null

Add PortsMovedAdd

Allow nulls on ports_moved.{from_port_id|to_port_id}
I suspect from_port_id cannot be NULL, but we'll adjust that later if need be.

When a new user is created, a watch list is automatically created too.
When this is done, ensure the in_service flag is set to true.  Otherewise,
they can't start adding things to their watch list.

See classes/watch_list_element.php::AddToDefault($UserID, $ElementID).

Remove commit_log_port_elements which has been replaced by commit_log_ports_elements
Add index commit_log_ports_elements_clid on
           commit_log_ports_elements (commit_log_id)

include upgrade instructions

Add tables for statistics

Add some missing drop triggers statements.

Ignore the backup files

*** empty log message ***

- remove housekeeping table, no longer used
- alter security_notice
- remove index on security_notice (commit_log_id)
  and leave it to the primary key instead.
- move indexes on ports_categories (port_id) and ports_categories (category_id)
- add commit_log_port_extras

Add login counts code

add FUNCTION freshports_commit(text) RETURNS SETOF freshports_commit_record

Add recent changes

Add element_type

Alter FUNCTION LastestCommits(int, int) to cater for non-port port commits.
Bring back the full pathname.

Add FUNCTION commit_log_ports_insert(int) RETURNS boolean
which adds a commit to the latest_commits_ports table

Add FUNCTION LastestCommits(int, int) RETURNS SETOF commit_record

Provide a better method for ensuring duplicates do not arrive in
daily_refreshes.

Triggers should be dropped before they are created.

Replace OPAQUE with TRIGGER on functions used as triggers.
remember to drop triggers before replacing them:
  DROP TRIGGER security_notice_audit ON security_notice;
  DROP TRIGGER ports_ports_categories ON ports;

remove: WatchListUserID
        RecordLastestPortCommits
        RecordLastestCommits

WatchListStagingClear should take an int8

ignore ? FreshPorts2.PDB
cvs server: Diffing .
Index: FreshPorts2.pdm
===================================================================
RCS file: /home/repositories/freshports-1/database-schema/FreshPorts2.pdm,v
retrieving revision 1.71
diff -r1.71 FreshPorts2.pdm
51c51
< -- $Id: FreshPorts2.pdm,v 1.71 2003/05/09 19:39:24 dan Exp $\
---
> -- $Id: FreshPorts2.pdm,v 1.70 2003/05/02 18:37:59 dan Exp $\
83c83
< 1052505034
---
> 1052509087
[dan@polo:/usr/websites/working-copy.freshports.org/database-schema] $ cd ..
[dan@polo:/usr/websites/working-copy.freshports.org] $ cd database-schema
[dan@polo:/usr/websites/working-copy.freshports.org/database-schema] $ cvs .ignore
ignore FreshPorts2.PDB

Example FreshPorts database

Add watch_list_count

comment out a message

Update GetPortID to treat virtual and physical categories differently.

Add the security notice report to the report table

comment out some debugging

In Stats_PortCountNewInterval, we shouldn't use SystemTimeAdjust

add users.page_size

Add security_notice_audit triggers

Let's do nice SQL

Add missing element_pathname which takes a boolean for leading /

start using triggers instead of rules on latest_commits_ports and latest_commits

Hmmm, when inserting, the check for old != new will fail, because there
is no old.

Add new trigger ports_categories_set

Add optional leading slash to Element_Pathname

Avoid a problem which occurs on the day after DST changes:
<               select (current_date - interval ''1 day'')::date
---
>               select (current_date - 1)::date

While here: whitespace adjustment

Add security_notice_audit

better logging

Add rules for commit_log_ports.  Perhaps these should be changed into triggers.

PR 251 - columns of user_tasks table should be user_id, task_id

whitespace only

Add GetPortID

use TRIGGER not OPAQUE
convert some stuff to upper case

add trigger on categories for virtual categories

convert some stuff to upper case, whitespace changes.

Changes for virtual categories.

rename ports_refresh_ignore (it was ports_refesh_ignore)

Start using serial so we can remove the add sequence commands
a few other random changes
Part of upgrading to PostgreSQL 7.3

It seems I deleted a great deal during a previous commit.

Some changes, don't know what...

redo the security notice tables to be more compact
rearrange the diagram for printing

add:
users.watch_list_add_remove
users.max_number_watch_lists

changes for multiple watch lists

We had two Stats_CommitCountPorts; rename one to Stats_CommitCount.

Add note about watch_list columns not all being used

Add a note about populating element_pathnames

Add element_pathnames

Log changes to users.email

Add revision_name

sorry, that should have been changes for freshsource

oops, use commits_recent_ports instead of commits_recent

latest version of report_log_latest

create Stats_PortCountNewInterval and modify  Stats_PortCountNewToday Stats_PortCountNewYesterday
and Stats_PortCountNewThisWeek to use it.

Add functions for more stats

Add index on ports (element_id)
Add graphs table

Ummm, let's only check parent status if we are dealing with a port here

Add status to ports_all

Add commits_latest_tmp.message_id so we can link from the webpage to the
mailing list archive.

remove ports2, categories2, and element2
no idea where they came from

Add Daily_Refreshes table and function for adding stuff to
and removing entries from.

- add committer to commits_latest
- set users.emailbouncecount default to zero
- add view ports_all

This commit was manufactured by cvs2svn to create branch 'FreshPorts2'.

Latest version

update PortVerifyBegin to use new ports_check strategy

use ports.revision/version if commit_log_ports value is null

Add commits_recent view used to ensure the last one hundred ports are listed.

Start using our view so we only get all the ports for the last 100 commits

If we try to add a file into the tree, and it's parent is a file,
change it to a directory rather than raise an exception.

Add functions for checking /usr/ports/INDEX

Let's start logging changes to the staging area.

WatchListStagingMergeWatchList is no longer used

OK, now we have staging area clear, and update watch list working.

major change to use pkg_info -qao

stuff for processing pkg_info uploading

Add ConfirmUserAccount

looks good for avoiding deadlock on slower machinesk

lastest and greatest

Add a new column to housekeeping, refresh_done.  Any time a port is refreshed,
this bit should be set.  It will be cleared by RecordLastestPortCommits.

Make sure we put the basic data into the system so it is usable.

Add two new tables: commits_latest housekeeping

use text instead of varchar in Element_Pathname

remove an unused ElementTagSet and modify the one which is used.
before we insert into system_branch_element_revision, ensure
the entry is not already there.

oops, don't do asc on indexes.

add default values for some timestamp field
add index on commit_log(message_id)

Includes commit_log_ports

Latest build

Add system.time_adjust field

Add user_confirmations table for email verification

directory_file_flag should be F or D.
make some RI constraints cascade not restrict

remove GetOrCreateCategory which was not used.
Remove quotes from field names which screwed up the category entries.

remember to set status in element.

rename system_version         to system_branch
rename system_version_element to system_branch_element_revision

remove some unneeded sequences

rename system_version         to system_branch
rename system_version_element to system_branch_element_revision

add ID to head of file.

add commit_log - commit_log_elements integrity

for latest changes

instructions for creating database schema

change ports table

Add CreatePort