From ff7e0357cd16672b35f720f128940bfaf732bd7a Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Wed, 14 Oct 2020 14:27:09 +0200 Subject: 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 --- Changes | 2 ++ lib/App/BorgRestore/DB.pm | 24 ++++++++++++++++++++++++ 2 files changed, 26 insertions(+) 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; -- cgit v1.2.3-24-g4f1b