Bug 21079: Unify metadata schema across backends

This patch contains a database upgrade that takes appropriate metadata
properties relating to articles in the FreeForm backend and creates
metadata that corresponds with the metadata being created by the BLDSS
backend. This enables us to create templates that can display metadata
equally across any backends that contain this metadata;

To test:
- Ensure you have at least one article request created with the FreeForm backend
- Check the metadata for the request:
  => TEST: You should have properties such as 'article_title', 'article_author' populated
  => TEST: You should NOT have properties such as 'container_title' &
  'pages'
- Run the upgrade
  => TEST: For the same requests you should now have the following
  properties (if their values were originally populated):
    - container_title (this should correspond with what *was* 'title')
    - title (this should correspond with 'article_title')
    - pages (this should correspond with 'article_pages')
    - author (this should correspond with 'article_author')

Signed-off-by: Stephen Graham <s.graham4@herts.ac.uk>

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>

diff --git a/installer/data/mysql/atomicupdate/bug_21079_map_illrequestattributes.perl b/installer/data/mysql/atomicupdate/bug_21079_map_illrequestattributes.perl
new file mode 100644 (file)
index 0000000..d7145cd
--- /dev/null
+++ b/installer/data/mysql/atomicupdate/bug_21079_map_illrequestattributes.perl
@@ -0,0 +1,93 @@
+use Try::Tiny;
+$DBversion = 'XXX';  # will be replaced by the RM
+if( CheckVersion( $DBversion ) ) {
+
+    # All attributes we're potentially interested in
+    my $ff_req = $dbh->selectall_arrayref(
+        'SELECT a.illrequest_id, a.type, a.value '.
+        'FROM illrequests r, illrequestattributes a '.
+        'WHERE r.illrequest_id = a.illrequest_id '.
+        'AND r.backend = "FreeForm"',
+        { Slice => {} }
+    );
+
+    # Before we go any further, identify whether we've done
+    # this before, we test for the presence of "container_title"
+    # We stop as soon as we find one
+    foreach my $req(@{$ff_req}) {
+        if ($req->{type} eq 'container_title') {
+            warn "Upgrade already carried out";
+        }
+    }
+
+    # Transform into a hashref with the key of the request ID
+    my $requests = {};
+    foreach my $request(@{$ff_req}) {
+        my $id = $request->{illrequest_id};
+        if (!exists $requests->{$id}) {
+            $requests->{$id} = {};
+        }
+        $requests->{$id}->{$request->{type}} = $request->{value};
+    }
+
+    # Transform any article requests
+    my $transformed = {};
+    foreach my $id(keys %{$requests}) {
+        if (lc($requests->{$id}->{type}) eq 'article') {
+            $transformed->{$id} = $requests->{$id};
+            $transformed->{$id}->{type} = 'article';
+            $transformed->{$id}->{container_title} = $transformed->{$id}->{title}
+                if defined $transformed->{$id}->{title} &&
+                    length $transformed->{$id}->{title} > 0;
+            $transformed->{$id}->{title} = $transformed->{$id}->{article_title}
+                if defined $transformed->{$id}->{article_title} &&
+                    length $transformed->{$id}->{article_title} > 0;
+            $transformed->{$id}->{author} = $transformed->{$id}->{article_author}
+                if defined $transformed->{$id}->{article_author} &&
+                    length $transformed->{$id}->{article_author} > 0;
+            $transformed->{$id}->{pages} = $transformed->{$id}->{article_pages}
+                if defined $transformed->{$id}->{article_pages} &&
+                    length $transformed->{$id}->{article_pages} > 0;
+        }
+    }
+
+    # Now write back the transformed data
+    # Rather than selectively replace, we just remove all attributes we've
+    # transformed and re-write them
+    my @changed = keys %{$transformed};
+    my $changed_str = join(',', @changed);
+
+    if (scalar @changed > 0) {
+        $dbh->{AutoCommit} = 0;
+        $dbh->{RaiseError} = 1;
+        try {
+            my $del = $dbh->do(
+                "DELETE FROM illrequestattributes ".
+                "WHERE illrequest_id IN ($changed_str)"
+            );
+            foreach my $reqid(keys %{$transformed}) {
+                my $attr = $transformed->{$reqid};
+                foreach my $key(keys %{$attr}) {
+                    my $sth = $dbh->prepare(
+                        'INSERT INTO illrequestattributes '.
+                        '(illrequest_id, type, value) '.
+                        'VALUES '.
+                        '(?, ?, ?)'
+                    );
+                    $sth->execute(
+                        $reqid,
+                        $key,
+                        $attr->{$key}
+                    );
+                }
+            }
+            $dbh->commit;
+        } catch {
+            warn "Upgrade to $DBversion failed: $_";
+            eval { $dbh->rollback };
+        };
+    }
+
+    SetVersion( $DBversion );
+    print "Upgrade to $DBversion done (Bug 21079 - Unify metadata schema across backends)\n";
+}