🗂️ Identifying Your TV Show Data in PostgreSQL

To locate the table where your TV show data is stored in PostgreSQL, follow these steps:

Step 1: List All Tables in the Database 🗃️

1. Access the PostgreSQL Prompt:

   docker exec -it riven-db psql -U postgres
   

2. Connect to Your Database:

   \c riven
   

3. List All Tables:

   Run the following command to view all tables in the current database:

   \dt
   

   This will display a list of tables along with their schema, name, type, and owner.

Step 2: Examine Table Structure 🔍

Once you have a list of tables, examine the structure of each to find the relevant columns for your TV show data.

1. Describe a Table:

   For example, to see the structure of the tvshows table:

   \d tvshows
   

   This command shows you the columns, their types, and any constraints (like primary keys).

Step 3: Look for Relevant Columns 🔑

When describing a table, look for columns that might contain your TV show data. Common names include:

• title
• name
• show_name
• description
• genre

Example Workflow 🛠️

Here’s how the entire process might look:

# Access the PostgreSQL prompt
docker exec -it riven-db psql -U postgres

# Connect to your database
\c riven

# List all tables
\dt

# Describe the 'tvshows' table to see its structure
\d tvshows

Conclusion 🏁

By following these steps, you can identify which table holds your TV show data and what columns are available for querying or deleting. If you find a table that seems relevant but you’re unsure, feel free to share its structure, and I can help you interpret it!

Troubleshooting Command Errors ⚠️

If you encounter issues with the command you entered, here’s how to resolve them:

Step 1: Ensure You Are in the Correct Environment ✅

Make sure you are in the PostgreSQL command prompt (psql). A prompt like riven=# indicates you’re in the right place.

Step 2: Execute the Query Again 🔄

Try running the query again:

SELECT _id FROM "MediaItem" WHERE title ILIKE '%Isekai%';

Step 3: Verify the Output 📊

If the command executes successfully, it should return the _id of any media items that match the title. If there are no results, it will simply return an empty set.

Checking for References to _id = 3944 🔗

You’ve successfully retrieved a list of tables referencing the MediaItem table. Here’s a summary:

Step 1: Query Each Table for _id = 3944 🔍

Run SELECT queries on each of these tables:

1. Movie:

   SELECT * FROM "Movie" WHERE _id = 3944;
   

2. Show:

   SELECT * FROM "Show" WHERE _id = 3944;
   

3. StreamBlacklistRelation:

   SELECT * FROM "StreamBlacklistRelation" WHERE media_item_id = 3944;
   

4. StreamRelation:

   SELECT * FROM "StreamRelation" WHERE parent_id = 3944;
   

5. Subtitle:

   SELECT * FROM "Subtitle" WHERE parent_id = 3944;
   

6. Season:

   SELECT * FROM "Season" WHERE _id = 3944;
   

7. Episode:

   SELECT * FROM "Episode" WHERE _id = 3944;
   

Step 2: Execute Queries 🏃‍♂️

Run these queries one by one in your PostgreSQL prompt to check for any records associated with _id = 3944.

Step 3: Review Results 📋

• Check each output to see if there are related records.
• If a table returns results, it indicates associations with the MediaItem having _id = 3944.

Deleting Shows and Related Data 🗑️

If you need to delete entries, follow these steps:

Step 1: Check Related Seasons 🔎

First, identify which seasons reference this show:

SELECT * FROM "Season" WHERE parent_id = 3944;

Step 2: Delete Related Seasons ❌

Once identified, delete those records:

DELETE FROM "Season" WHERE parent_id = 3944;

Step 3: Delete the Show 🎬

After deleting related seasons, you can delete the show itself:

DELETE FROM "Show" WHERE _id = 3944;

Complete Workflow 🔄

1. Check for Related Seasons:

   SELECT * FROM "Season" WHERE parent_id = 3944;
   

2. Delete Related Seasons (if found):

   DELETE FROM "Season" WHERE parent_id = 3944;
   

3. Delete the Show:

   DELETE FROM "Show" WHERE _id = 3944;
   

Important Considerations ⚠️

• Backup: Always ensure you have a backup before performing delete operations.
• Cascade Deletion: Consider setting the foreign key constraint in the Season table to ON DELETE CASCADE for future deletions.

Here’s a more compact version for easy copy and paste:

🐳 Docker Command entering postgresql.

docker exec -it riven-db psql -U postgres

📜 SQL Queries

Media Items Search

SELECT _id FROM "MediaItem" WHERE title ILIKE '%Ganbare Doukichan%';
SELECT * FROM "MediaItem" WHERE title ILIKE '%The Lord of the Rings: The Rings of Power%';

Check Parent IDs

\d "Episode"

🔍 Info for Media Item _id = 1314

SELECT * FROM "Movie" WHERE _id = 1314;
SELECT * FROM "StreamBlacklistRelation" WHERE media_item_id = 1314;
SELECT * FROM "StreamRelation" WHERE parent_id = 1314;
SELECT * FROM "Subtitle" WHERE parent_id = 1314;
SELECT * FROM "Show" WHERE _id = 1314;
SELECT * FROM "Season" WHERE parent_id = 1314;
SELECT * FROM "Season" WHERE _id = 1314;
SELECT * FROM "Episode" WHERE parent_id = 1314;

🗑️ Deleting Records

DELETE FROM "Episode" WHERE parent_id IN (SELECT _id FROM "Season" WHERE parent_id = 4156);
DELETE FROM "Season" WHERE parent_id = 4156;
DELETE FROM "Show" WHERE _id = 4156;
DELETE FROM "Movie" WHERE _id = 3384;
DELETE FROM "Subtitle" WHERE parent_id IN (SELECT _id FROM "Season" WHERE parent_id = 4156);
DELETE FROM "StreamRelation" WHERE parent_id = 4156;
DELETE FROM "StreamBlacklistRelation" WHERE media_item_id = 4156;
DELETE FROM "MediaItem" WHERE _id = 4156;

You can easily copy and paste this format! 😊