# Iceshrimp.NET SQL Snippets

## Introduction:

A selection of SQL scripts for the Iceshrimp.NET PostgreSQL database

Some of the SQL scripts need the "userId"

This can be found from the Iceshrimp Homepage:

* Profile | 3 little round buttons | About | ID

  *(The ID is the 10 digit alphanumeric string)*

## Files:

* ### [CREATE_blocked_instance.sql]

	Recreates the 'blocked_instance' table in case of bungling. Run as the user that owns the database (i.e. iceshrimp)

* ### [DELETE_ANALYZE_deep_clean.sql]

	<span style="color: red;">**WARNING**</span>

	This script will DELETE and then ANALYZE a few of the frequently populated tables. Use with care

* ### [DELETE_VACUUM_public_note.sql]

	<span style="color: red;">**WARNING**</span>

	This script will DELETE and then VACUUM the rows in 'pubic.note' that are over 10 days old

* ### [INSERT_INTO_blocked_instance.sql]

	This script copies the contents of the 'blockedHosts' field in the 'meta' table to the 'blocked_instance' table

* ### [SELECT_last_followee_note_dated.sql]

	Links the 'note', 'user' and 'following' tables to show the last date that a followee posted

* ### [SELECT_last_follower_note_dated.sql]

	Links the 'note', 'user' and 'following' tables to show the last date that a follower posted

## Maintenance:

The next three scripts are the routines that I run daily, hourly and monthly:

* ### [TIDY_daily.sql]

	<span style="color: red;">**WARNING**</span>

	This script will:

	* DELETE FROM the 'public.drive_file' table where the records are not owned by specified accounts (configurable)

	* DELETE FROM the 'public.user' table where the records are not owned by an instance account i.e. remove any accounts that are neither followers nor followees
	
		(Fediverse accounts that are blocked and or muted need to be retained in the 'public.user' table)

	* DELETE FROM the 'public.user' table any accounts that have moved (specifically, the 'movedToUri' has been set)

	* DELETE FROM the 'public.session' table retaining any session tokens that are in constant use e.g. bots

	* TRUNCATE a couple of tables to clear any sessions (specifically, the 'public.oauth_app', the 'public.oauth_token' and the 'public.push_subscription' tables)

	* VACUUM ANALYZE the whole database

* ### [TIDY_hourly.sql]

	<span style="color: red;">**WARNING**</span>

	This script will:

	* DELETE the rows from 'public.note', 'public.note_like', 'public.note_reaction' and 'public.note_watching' that are over 5 days old, then perform an ANALYZE on those same tables:

* ### [TIDY_monthly.sql]

	* Perform a VACUUM FULL ANALYZE on the database and only needs to be run occasionally: