DB: Remove columns left over by migrations 3/4

It's not super bad that these are there, but they do increase the
database size a fair bit considering that they are actually just full of
NULL values.

Signed-off-by: Florian Pritz <bluewind@xinu.at>

2 files changed, 26 insertions, 0 deletions

diff --git a/Changes b/Changes
index edd7841..e7582ea 100644
--- a/Changes
+++ b/Changes
@@ -3,6 +3,8 @@ Revision history for Perl extension App-BorgRestore
 {{$NEXT}}
 	- Require DBD::SQLite 1.60 or newer to fix issues with schema migration 2
 	  on old systems.
+	- Reduce database size for databases that contained data from before
+	  migrations 3 and 4.
 
 3.4.3 2020-09-27T13:53:54Z
 	- Add database migration for change from 3.4.2
diff --git a/lib/App/BorgRestore/DB.pm b/lib/App/BorgRestore/DB.pm
index 6fb8c7c..2999eb4 100644
--- a/lib/App/BorgRestore/DB.pm
+++ b/lib/App/BorgRestore/DB.pm
@@ -95,6 +95,30 @@ method _migrate() {
 			$self->{dbh}->do('delete from `archives`');
 			$self->{dbh}->do('delete from `files`');
 		},
+		5 => sub {
+			# Remove columns left over by migrations 3 and 4 from files tables
+			my @archive_ids;
+			my $st = $self->{dbh}->prepare("select `id` from `archives`;");
+			$st->execute();
+			while (my $result = $st->fetchrow_hashref) {
+				push @archive_ids, $result->{id};
+			}
+
+			$self->{dbh}->do('create table `files_new` (`path` text, primary key (`path`)) without rowid;');
+			for my $archive_id (@archive_ids) {
+				$archive_id = untaint($archive_id, qr(.*));
+				$self->{dbh}->do('alter table `files_new` add column `'.$archive_id.'` integer;');
+			}
+
+			if (@archive_ids > 0) {
+				my @columns_to_copy = map {'`'.$_.'`'} @archive_ids;
+				@columns_to_copy = ('`path`', @columns_to_copy);
+				$self->{dbh}->do('insert into `files_new` select '.join(',', @columns_to_copy).' from files');
+			}
+
+			$self->{dbh}->do('drop table `files`');
+			$self->{dbh}->do('alter table `files_new` rename to `files`');
+		},
 	};
 	my $ran_migrations = 0;