DB Migration `20240305120015_torrust_add_independent_root_hash_field.sql` truncates data for torrent secondary tables

[josecelano]

The latest migrations added in this PR #516 produce data lost in the database.

Since SQLite does not support changing the table schema you have to

• create a new table
• copy data from the old table to the new table
• drop the old table

When you drop the old table an implicit DELETE query is executed for the master table and it triggers DELETE queries for secondary tables that contain foreign keys to the primary table torrust_torrents with ON DELETE CASCADE

The official documentation about DROP is not clear (at least for me). In this site it explains the problem.

However, if the foreign key constraint is defined with ON DELETE CASCADE, then the parent table will be dropped, and any rows that reference that table’s primary key column will be deleted in the child table.

We have to change the migration to avoid deleting secondary tables.

Links

• https://database.guide/sqlite-drop-table/
• https://www.sqlite.org/lang_droptable.html
• https://www.sqlitetutorial.net/sqlite-drop-table/
• https://sqldocs.org/sqlite/sqlite-drop-table/#dependent-objects
• https://database.guide/sqlite-drop-table/

[josecelano]

I should have made this change progressively. Instead of doing this in one single migration:

• Add new field root_hash.
• Change pieces column to make it nullable.

I should have made two smaller steps:

Migration 1:

• Add new column root_hash.
• Add new column pieces2 nullable. Copy data from column pieces to pieces2

Migration 2:

• Drop field pieces.
• Rename field pieces2 to pieces.

This does not require to drop the table and it's safer.