From 4eaf6823cbf2c2d332b317e6f6f755b23b6a1d1f Mon Sep 17 00:00:00 2001 From: Florian Pritz Date: Sat, 28 Sep 2019 10:53:17 +0200 Subject: DB: Convert timestamp column names to numeric IDs Having the backup archive names in the table columns is not strictly necessary. Replace this with numeric IDs and map them via the `archives` table so that we can eventually remove untaint_archive_name(). Signed-off-by: Florian Pritz --- lib/App/BorgRestore/DB.pm | 30 +++++++++++++++++++++++------- 1 file changed, 23 insertions(+), 7 deletions(-) diff --git a/lib/App/BorgRestore/DB.pm b/lib/App/BorgRestore/DB.pm index fbbd1f7..fe85c4d 100644 --- a/lib/App/BorgRestore/DB.pm +++ b/lib/App/BorgRestore/DB.pm @@ -63,6 +63,24 @@ method _migrate() { $self->{dbh}->do('create table if not exists `files` (`path` text, primary key (`path`)) without rowid;'); $self->{dbh}->do('create table if not exists `archives` (`archive_name` text unique);'); }, + 2 => sub { + $self->{dbh}->do('alter table `archives` rename to `archives_old`'); + $self->{dbh}->do('create table `archives` (`id` integer primary key autoincrement, `archive_name` text unique);'); + $self->{dbh}->do('insert into `archives` select null, * from `archives_old`'); + $self->{dbh}->do('drop table `archives_old`'); + + my $st = $self->{dbh}->prepare("select `archive_name` from `archives`;"); + $st->execute(); + while (my $result = $st->fetchrow_hashref) { + my $archive = $result->{archive_name}; + # We trust all values here since they have already been + # sucessfully put into the DB previously. Thus they must be + # safe to deal with. + $archive = App::BorgRestore::Helper::untaint($archive, qr(.*)); + my $archive_id = $self->get_archive_id($archive); + $self->{dbh}->do("alter table `files` rename column `timestamp-$archive` to `$archive_id`"); + } +}, }; for my $target_version (sort { $a <=> $b } keys %$schema) { @@ -157,14 +175,12 @@ method remove_archive($archive) { $st->execute($archive); } -fun _prefix_archive_id($archive) { - $archive = App::BorgRestore::Helper::untaint_archive_name($archive); - - return 'timestamp-'.$archive; -} - method get_archive_id($archive) { - return _prefix_archive_id($archive); + my $st = $self->{dbh}->prepare("select `id` from `archives` where `archive_name` = ?;"); + $archive = App::BorgRestore::Helper::untaint($archive, qr(.*)); + $st->execute($archive); + my $result = $st->fetchrow_hashref; + return App::BorgRestore::Helper::untaint($result->{id}, qr(.*)); } method get_archives_for_path($path) { -- cgit v1.2.3-24-g4f1b